question

sqlLearner 1 avatar image
sqlLearner 1 asked

SQL: Distinct Count with GROUPING

Hi I am trying to write a query that counts all the distinct dates in a specific month in which a product is used and comapres it aginst another month and take the percentagedifference: I don't need to know what product is used as long as a product has been used. I want to Group based on ACCTID and COUNTRY. The problem I am having is each ACCTID can have multiple SITEID..Each SITEID can be in a different country. When there is Multiple SITEIDS in different countries it is not returning the correct number of days the product is used.
sqlcounting
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kevin Feasel avatar image
Kevin Feasel answered
Here's a slightly modified example with some data attached: declare @t1 table ( AcctId int, SiteId int, Country char(2), ItemId int, UsageDate date ); insert into @t1 values (123, 321, 'US', 69, '2011-05-15'), (123, 321, 'US', 73, '2011-05-15'), (123, 321, 'US', 69, '2011-05-16'), (123, 456, 'FR', 76, '2011-05-18'), (321, 789, 'US', 53, '2011-05-17'), (123, 321, 'US', 69, '2011-05-05'), (123, 321, 'US', 73, '2011-05-05'), (123, 321, 'US', 69, '2011-05-06'), (123, 456, 'FR', 76, '2011-05-08'), (321, 789, 'US', 53, '2011-05-07'), (123, 321, 'US', 69, '2011-06-15'), (123, 321, 'US', 73, '2011-06-15'), (123, 321, 'US', 69, '2011-06-16'), (123, 456, 'FR', 76, '2011-06-18'), (321, 789, 'US', 53, '2011-06-17'), (123, 321, 'US', 69, '2011-04-15'), (123, 321, 'US', 73, '2011-04-15'), (123, 321, 'US', 69, '2011-04-16'), (123, 456, 'FR', 76, '2011-04-18'), (321, 789, 'US', 53, '2011-04-17'); with records as ( select AcctId, Country, MONTH(UsageDate) as mon, COUNT(distinct UsageDate) as Count from @t1 group by AcctId, Country, MONTH(UsageDate) ) select rCurrent.AcctId, rCurrent.Country, rCurrent.mon as CurrentYearMonth, rCurrent.Count as CurrentMonthTotal, rPrevious.mon as PreviousYearMonth, rPrevious.Count as PreviousMonthCount, CAST( 100 * (rCurrent.Count * 1.0 / NULLIF(rPrevious.Count, 0) - 1) as decimal (10, 2)) AS DifferenceInPercent from records rCurrent left outer join records rPrevious on rCurrent.AcctId = rPrevious.AcctId and rCurrent.Country = rPrevious.Country Could you perhaps give an example of data which is being aggregated incorrectly?
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlLearner 1 avatar image sqlLearner 1 commented ·
Thanks for the help I figured out my own problem. I was Grouping by a wrong item.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.