August 29, 2002 at 2:41 pm
I have this select statement:
SELECT termnum,username,page,count(page) from trackagent where datehit between '1/1/02' AND '8/1/02' AND termnum <> '' AND termnum <> '373' group by termnum,username,page WITH ROLLUP
When I run that piece of code through the query analyzer, it runs and when I look at the result sets. For each termnum I have, I get 2 subtotals. One with the termnum and one that has a null before continuing on with the next terminal number.
Is that the way its supposed to work? I thought you should get 1 subtotal per termnum not 2 with the exception of the last one when it does the grand total.
Matt
August 29, 2002 at 3:11 pm
What you are getting is a subtotal first for each page within a specific termnum and username, then another sub_total for a each username, and finally a grand total for all pages selected. Therefore if each termnum only has a single username it will seem like you get two subtotals. If you have multiple usernames then it might look a little different.
Basically you get a subtotal for each item you group by.
Here is a query against the pubs database that might help understand why you get multple subtotals. Hope this helps.....
use pubs
go
-- note the two different sub-total amounts for title_id PS2091
-- where title_id 'BU1032' has the same amount
select title_id, ord_date,payterms, sum(qty)
from sales
where title_id = 'PS2091' or title_id = 'BU1032'
group by title_id, ord_date, payterms
with rollup
-------------------------
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
August 29, 2002 at 3:13 pm
Keep in mind you have three fields and a count column. I would expect to see something like this at minimum if the termnum was unique and only had one username returning
termnum, username, page, count(page)
termnum, username, null, count(page)=subtotal page
termnum, null, null, count(page)=subtotal username, page
---------------------------------------------------
termnum, username, page, count(page)
termnum, username, null, count(page)=subtotal page
termnum, null, null, count(page)=subtotal username, page
---------------------------------------------------
null, null, null, count(page)=grandtotal
This is show each termnum, with only one username returned and only one page for that user returned.
However I don't have a test server at home right now so my visual from memory may be wrong.
What does your result set look like?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 08/29/2002 3:13:45 PM
August 30, 2002 at 7:05 am
Well my result set looks like this:
termnum,username,page
termnum,username,null
termnum,null,null
--------------------
termnum,username,page
termnum,username,null
termnum,null,null
null,null,null
hmm I see whats going on now. Termnum is unique but I could have multiple username's. So its calculating the totals for that particular username as well as a total of all usernames under that termnum then of course the grandtotals of everything at the end.
hmm so its going to be impossible to just get it to have 1 subtotal after each termnum using rollup since I need all my fields in my group by. Though I wager using rollup is probably still more effient than looping through and calculating it myself.
Matt
August 31, 2002 at 8:26 pm
You might could query out the rest by either doing the as a subquery or as part of the query.
WHERE (termnum IS NOT NULL AND username IS NOT NULL AND page IS NOT NULL) OR (termnum IS NULL AND username IS NULL AND page IS NULL)
That should keep all except base positions and grand total. Or maybe
SELECT termnum, username, page, count(page) total FROM ......... GROUP BY termnum, username, page
UNION
SELECT NULL, NULL, NULL, count(page) total FROM .........
Should give same unless I typoed or made a logic error.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply