none
SQL Query to Count Distinct RRS feed

  • Question

  • Hi All,

    I would need your help to count distinct in Totals.

    Problem Statement:

    Invoice Data.

    to simplify the problem let us assume a Country had only 1 invoice generated for a day and it had 2 Items.

    1 from Food Category and 1 from ALC category.

    When I write count of Invoice at Country level (group by Country) I get 1 Invoice but when I add Category in group by it gives 1 in ALC and 1 in FOOD which is correct. What I need is 1 for ALC and 1 for FOD and a Total ROW with 1 since total is at Country level and number should match irrespective of Levels 


    AJ

    Tuesday, May 21, 2019 2:51 PM

Answers

  • Please check if this helps

    CREATE TABLE #Invoice (Country VARCHAR(100), InvoiceNumber INT)

    INSERT INTO #Invoice VALUES ('India',1)

    CREATE TABLE #InvoiceCat (InvoiceNumber INT, Category VARCHAr(100))

    INSERT INTO #InvoiceCat VALUES (1,'Food')

    INSERT INTO #InvoiceCat VALUES (1,'All')

    SELECT

           a.Country,

           COUNT(DISTINCT a.InvoiceNumber) AS InvoiceCount,

           b.Category,

           COUNT(b.Category  ) AS CategoryCount

    FROM #Invoice a

    INNEr JOIN #InvoiceCat b

           ON a.InvoiceNumber = b.InvoiceNumber

    GROUP BY      a.Country,

           b.Category


    Suresh
    My Scribbling

    Tuesday, May 21, 2019 3:27 PM

All replies

  • Please check if this helps

    CREATE TABLE #Invoice (Country VARCHAR(100), InvoiceNumber INT)

    INSERT INTO #Invoice VALUES ('India',1)

    CREATE TABLE #InvoiceCat (InvoiceNumber INT, Category VARCHAr(100))

    INSERT INTO #InvoiceCat VALUES (1,'Food')

    INSERT INTO #InvoiceCat VALUES (1,'All')

    SELECT

           a.Country,

           COUNT(DISTINCT a.InvoiceNumber) AS InvoiceCount,

           b.Category,

           COUNT(b.Category  ) AS CategoryCount

    FROM #Invoice a

    INNEr JOIN #InvoiceCat b

           ON a.InvoiceNumber = b.InvoiceNumber

    GROUP BY      a.Country,

           b.Category


    Suresh
    My Scribbling

    Tuesday, May 21, 2019 3:27 PM
  • Hi Suresh,

    I have a single table which has Invoice information at Item level.

    There is dimension for Item and another for Category so I need to join Fact and Category Dimension on Item id and get the Invoice Number .


    AJ

    Monday, May 27, 2019 2:35 PM