1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Group by, count distinct and take care when no records are returned

Discussion in 'SQL PL/SQL' started by ketangarg86, May 4, 2015.

  1. ketangarg86

    ketangarg86 Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I am stuck in a problem. I have a table which has 2 column, date in MM/DD/YYYY format and Name of a Customer in another column.

    I am able to find the distinct number of customers sending order by month. The problem, I am having is that in case there is no customer in a particular month, no records is displayed instead of the below. Could you please help.

    Below should be displayed instead of no records in case there is no distinct customer for May 2015.
    Month Senders
    2015-05 0

    Below is my query
    SELECT to_char(DAY_DATE, 'YYYY-MM') AS Month, count(distinct name) as Senders FROM MESSAGE_COUNT
    WHERE to_char(DAY_DATE, 'MM-YYYY') = to_char(sysdate, 'MM-YYYY')
    group by to_char(DAY_DATE, 'YYYY-MM')
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    The WHERE clause on your example query implies that you are always expecting a single row (i.e. sales for the current month). Is this the case? If you always expect to receive one row, handling a month with no records is fairly easy. If your example query is not typical and you might be querying for multiple months, where one or more of the months being returned might be zero -- the problem changes, and becomes somewhat harder.

    The following is a solution assuming you will always be using the current month.

    Code (Text):
    CREATE TABLE message_count (
    day_date    DATE,
    mc_name     VARCHAR2(20));

    table MESSAGE_COUNT created.

    WITH month_row AS (
      SELECT TO_CHAR(SYSDATE, 'YYYY-MM') currmon
      FROM   dual)
    SELECT mr.currmon,
           COUNT(DISTINCT mc.mc_name) AS SENDERS
    FROM   month_row mr
           LEFT JOIN message_count mc
           ON mr.currmon = TO_CHAR(day_date, 'YYYY-MM')
    GROUP BY mr.currmon;

    CURRMON    SENDERS
    ------- ----------
    2015-05          0
     
  3. Vicky

    Vicky Forum Advisor

    Messages:
    261
    Likes Received:
    11
    Trophy Points:
    260
    Location:
    Chennai
    I'm using the below query to get count of records on each month..


    Code (SQL):


    WITH MONTH_ROW AS (
      SELECT TO_CHAR(sysdate, 'YYYY-')||LPAD(level,2,0) CURRMON
      FROM   dual CONNECT BY level<=12)
    SELECT mr.currmon,
           COUNT(DISTINCT mc.mc_name) AS SENDERS
    FROM   month_row mr
           LEFT JOIN message_count mc
           ON MR.CURRMON = TO_CHAR(DAY_DATE, 'YYYY-MM')
    GROUP BY mr.currmon;



    CURRMON SENDERS
    ------- -------
    2015-01       1
    2015-02       1
    2015-03       2
    2015-04       0
    2015-05       0
    2015-06       1
    2015-07       0
    2015-08       1
    2015-09       0
    2015-10       0
    2015-11       0
    2015-12       0


     

    And, I'm not using any where clause here ..... Is der any alternative for this query,?!/
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    There are other ways to do it, yes. However, the big question is what the OP needs to do. The query you provided generates rows for the 12 months of the current year. However, if the requirement is for example to show the preceding 12 months (i.e. May 2014 -> May 2015) it won't produce the desired results. The reason I indicated that multiple months is harder is not because it is difficult generating multiple rows, but because it introduces another variable (namely which months), which must be answered before a query can be designed.