CODEWITHSUNDEEP
reporting-servicesgroup-by
Benzz
Benzz

Reputation: 119

GROUP BY more than one field?

I'm wondering if anyone can help with a different way around my issue.

What I have currently is:

SELECT
       [ItemDescription]
      ,COUNT(*) AS 'Amount'
FROM _shl_SalesOrderSummary ssos
WHERE ssos.[ItemDescription] 
       IN (SELECT [ItemDescription] FROM [_shl_SalesOrderSummary]
       WHERE ItemDescription LIKE '%FFP3 DUST MASK%' OR
       ItemDescription LIKE '%EAR DEFENDERS%' OR
       ItemDescription LIKE 'SAFETY GOGGLES'
       GROUP BY [ItemDescription])
GROUP BY ssos.[ItemDescription]

This brings me the following results:

ItemDescription                          Amount
EAR DEFENDERS                             473
Ear defenders (helmet)                      1
FFP3 Dust masks (SPECIAL ORDER)             1
safety goggles                              1

My issue is, I need the date to be able to add a parameter in a report.

When this is added, I can't seem to figure out how to implement it properly?

e.g:

SELECT TOP 50
       [Order Date]
      ,[ItemDescription]
      ,COUNT(*)
FROM _shl_SalesOrderSummary ssos
WHERE ssos.[ItemDescription] 
       IN (SELECT [ItemDescription] FROM [_shl_SalesOrderSummary]
       WHERE ItemDescription LIKE '%FFP3 DUST MASK%' OR
       ItemDescription LIKE '%EAR DEFENDERS%' OR
       ItemDescription LIKE '%SAFETY GOGGLES%'
       GROUP BY [ItemDescription])
GROUP BY ssos.[ItemDescription], ssos.[Order Date]

My dateset turns into this when the date is added groups by date rather than all as one in the previous example:

Order Date              ItemDescription Amount
2014-03-15 00:00:00      EAR DEFENDERS    15
2014-03-17 00:00:00      EAR DEFENDERS    10
2014-03-19 00:00:00      EAR DEFENDERS    22

Any guidance or help would be appreciated - Thank you.

Upvotes: 0

Views: 37

Answers (1)

Paul Bambury
Paul Bambury

Reputation: 1312

Your query is fine. Now you need to group the dataset in the report on the ItemDescription field

https://msdn.microsoft.com/en-us/library/dd255263.aspx

Upvotes: 1

Related Questions