Im trying to group by Source then Date, then Status and get Counts for each. This is working in my code:
SELECT sourceCode as "Source", to_char(myTimestamp, 'YYYY-MM-DD') as "Date", statusCode as "Status", count(*) as "Count"
FROM archive_table
WHERE myTimestamp BETWEEN TO_TIMESTAMP('2013-09-30','yyyy-mm-dd') AND TO_TIMESTAMP('2013-10-05','yyyy-mm-dd')
GROUP BY sourceCode, to_char(myTimestamp, 'YYYY-MM-DD'), statusCode
ORDER BY 1, 2, 3
Here is the output:
Source Date Status Count
Source1 9/30/2013 C 10
Source1 10/1/2013 C 8
Source1 10/2/2013 C 24
Source1 10/2/2013 O 4
Source1 10/3/2013 C 19
Source1 10/3/2013 O 6
Source1 10/4/2013 C 5
Source1 10/4/2013 O 8
Source2 10/4/2013 C 22
Source2 10/4/2013 O 7
Source3 10/1/2013 C 2
Source4 9/30/2013 C 15
Source4 9/30/2013 O 15
Source4 10/1/2013 C 24
Source4 10/1/2013 O 12
Source4 10/2/2013 C 18
Source4 10/2/2013 O 8
Source4 10/3/2013 C 23
Source4 10/3/2013 O 13
However I would like to get a SUM or total count of each Source, each Date, each Status. I tried WITH ROLLUP at the endof the GROUP BY but received Oracle error.
I would like the output would give the above results AND some additional counts like this:
Source Date Status Count
Source1 84 --Shows counts for each source
Source2 29
Source3 2
Source4 128
Source1 O 18 --Show counts each source by status
Source2 C 7
Source3 O 2
Source3 C 0
Source4 O 48
Source4 C 80
all src 9/30/2013 O 15 --Show counts each source by date & status
all src 9/30/2013 C 25
all src 10/1/2013 O 12
all src 10/1/2013 C 20
all src 10/2/2013 O 15
all src 10/2/2013 C 10
all src 10/3/2013 O 0
all src 10/3/2013 C 25
all src 10/4/2013 O 15
all src 10/4/2013 C 13
Perhaps this is not even possible WITH ROLLUP
Im not sure if this is the appropriate solution that I found it returns results I am having trouble reading:
SELECT sourceCode as "Source", to_char(myTimestamp, 'YYYY-MM-DD') as "Date", statusCode as "Status", count(*) as "Count"
FROM archive_table
WHERE myTimestamp BETWEEN TO_TIMESTAMP('2013-09-30','yyyy-mm-dd') AND TO_TIMESTAMP('2013-10-05','yyyy-mm-dd')
GROUP BY
ROLLUP (sourceCode, to_char(myTimestamp, 'YYYY-MM-DD'), statusCode)
ORDER BY 1, 2, 3
And these are the output results:
Source Date Status Count
Source1 10/1/2013 8
Source1 10/2/2013 C 24
Source1 10/2/2013 O 4
Source1 10/2/2013 28
Source1 10/3/2013 C 19
Source1 10/3/2013 O 6
Source1 10/3/2013 25
Source1 10/4/2013 C 5
Source1 10/4/2013 O 8
Source1 10/4/2013 13
Source1 84
Source2 10/4/2013 C 22
Source2 10/4/2013 O 7
Source2 10/4/2013 29
Source2 29
Source3 10/1/2013 C 2
Source3 10/1/2013 2
Source3 2
Source4 9/30/2013 C 15
Source4 9/30/2013 O 15
Source4 9/30/2013 30
Source4 10/1/2013 C 24
Source4 10/1/2013 O 12
Source4 10/1/2013 36
Source4 10/2/2013 C 18
Source4 10/2/2013 O 8
Source4 10/2/2013 26
Source4 10/3/2013 C 24
Source4 10/3/2013 O 12
Source4 10/3/2013 36
Source4 10/4/2013 C 30
Source4 10/4/2013 O 11
Source4 10/4/2013 41
Source4 169
284
Try this out: http://sqlfiddle.com/#!4/98d9e/6/0.
I used a sum instead of a count because i modeled your first output. I didn't want to put in the raw data.
Egor's comment was correct, use the cube function to get all the various combinations between Source, Date, and Status. The number of rows for cube will be 2 ^ n where n = number of columns in the cube statement. The empty columns represent rows for a subtotal. The decode statement in the fiddle takes care of that and puts in 'all ...' for the subtotals. This can also be helpful if you need to find specific subtotals with an outer query.
The Oracle documentation has some pretty good examples of cube. http://docs.oracle.com/cd/E11882_01/server.112/e25554/aggreg.htm#DWHSG8614.