How to use rollup in SQL in select groupby

Question

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

Have you tried `GROUP BY cube(sourceCode, to_char(myTimestamp, 'YYYY-MM-DD'), statusCode)`? — Egor Skriptunoff, Oct 09 '13 at 17:50
@EgorSkriptunoff Yes I did the rollup I pasted my results but I do not understand how to interpret them to see if its what I need as it just gives a bunch of "blank" columns with numbers at the end — Kairan, Oct 09 '13 at 17:52
@EgorSkriptunoff Actually I need to correct myself, I thought you said "GROUP BY" I didnt see "GROUP BY cube". I will give a try and see what results I get. — Kairan, Oct 09 '13 at 23:26

score 1 · Accepted Answer · answered Oct 09 '13 at 23:29

1

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.

answered Oct 09 '13 at 23:29

Sean Dulin

51
2

Hi - can you explain the 1 in Decode? I looked at example of Decode online and it says it should be a value you are searching for: DECODE( expression , search , result [, search , result]... [, default] ) http://www.techonthenet.com/oracle/functions/decode.php – Kairan Oct 10 '13 at 00:17
decode is kind of like a fancy nvl check. You're right when you said that you give it an expression, a search, what to do if it finds the search, and a result if it doesn't find it. The grouping clause returns 1 for an aggregate row and 0 for a detail row. The decode is saying if aggregate row then use a result of 'all...' instead of the null that would be there otherwise use what's there (Source1 or Source2 or C or O, etc). – Sean Dulin Oct 10 '13 at 13:27
+1 and accept =) Thats great - thanks it works well, I just need to probably modify a subquery so that I can get it to return more details (that is for example 9/30/13 there are no Source1 Status='O' so it doesnt show up to display 0, thats unrelated though and will just need to work on my own for that i suppose – Kairan Oct 10 '13 at 13:51
You might be able to use a having clause to limit those out. having count(*) > 0 or whatever. I'm not positive without checking using SQLFiddle or an actual instance. Also, cube is really a helper for rollup, so you could use multiple rollups with the combinations that you want. cube(source, date, status) is really 8 rollup commands. rollup(source), rollup(source, date), rollup(source, status), etc. – Sean Dulin Oct 10 '13 at 14:27

How to use rollup in SQL in select groupby

1 Answers1