GROUP BY in sql to get monthly breakdown of data

Question

Say I have a data that describes different items sold and when they were sold. I want to breakdown this data and count different items sold on monthly basis. So here is what I have so far:

SELECT 
 ItemDescription
,OrderReceivedData 
,COUNT(*) AS ItemCount 
INTO 'C:\Users\whatever.csv' 
FROM 'C:\user\inputFileHere.csv' 
GROUP BY ItemDescription, OrderReceivedDate 
HAVING OrderReceivedDate LIKE '%2011%'

Now the thing is that my dates are in a bad format. So what the query above does is that it shows count for an item on 01JAN2011, 02JAN2011, ... , 10FEB2011, ...and so on. But what I want is the count for JAN2011, FEB2011, MAR2011... and so on. So basically I dont wanna GROUP BY OrderReceivedData but I want to Group by these specific 7 characters in OrderReceivedDate so I can ignore the dates. I hope it makes sense. So how do I do this?

score 0 · Answer 1 · answered Jun 13 '14 at 17:10

The simple approach, although a bit of a hack, is that you need to parse out the date characters, then group by that. For simplicity, you can reference the column by number. If you think this will change, repeat the parsing logic in your GROUP BY clause. This assumes the field contains two leading characters:

SELECT 
 ItemDescription
,RIGHT(OrderReceivedData, LEN(OrderReceivedData) - 2) AS MonthOrderReceivedData
,COUNT(*) AS ItemCount 
INTO 'C:\Users\whatever.csv' 
FROM 'C:\user\inputFileHere.csv' 
GROUP BY ItemDescription, 2
HAVING OrderReceivedDate LIKE '%2011%'

I did not test this code, but should get you on the right track.

I tried SUBSTRING instead of RIGHT because my date are like this : 01JAN201100:00:00. Its weird I know. But when I ran this query: "SLECT ItemDescription, SUBSTRING(OrderReceivedDate, 3, 7) AS OrderReceivedDateUpdated..." I got an error saying "Cannot find a valid : 'SUBSTRING(OrderReceivedDate,' — Sahil Chaudhary, Jun 13 '14 at 17:16

score 0 · Answer 2 · answered Jun 14 '14 at 08:06

You first need to make use Log Parser undestands your OrderReceivedDate as a timestamp, and then you format it back as year-month and group by it:

SELECT
   ItemDescription,
   Month,
   COUNT(*) AS TOTAL
USING
   TO_STRING(TO_TIMESTAMP(OrderReceivedDate,'ddMMMyyyy'), 'yyyy-MM') as Month
INTO 
   'C:\Users\whatever.csv' 
FROM 
   'C:\user\inputFileHere.csv' 
WHERE
   OrderReceivedDate LIKE '%2011%'
GROUP BY 
   ItemDescription,
   Month

score -1 · Accepted Answer · answered Jun 13 '14 at 17:29

-1

SELECT 
 ItemDescription
,SUBSTR(OrderReceivedDate,2,7) AS OrderReceivedDateUpdated 
,COUNT(*) AS ItemCount 
INTO 'C:\Users\whatever.csv' 
FROM 'C:\user\inputFileHere.csv' 
GROUP BY ItemDescription, OrderReceivedDateUpdated 
HAVING OrderReceivedDate LIKE '%2011%'

answered Jun 13 '14 at 17:29

Sahil Chaudhary

493
2
10
29

This answer needs some explanation. Someone flagged it as low quality. – JasonMArcher Jun 13 '14 at 18:24

GROUP BY in sql to get monthly breakdown of data

3 Answers3