View Single Post
  #6 (permalink)  
Old October 23rd, 2006, 12:38 PM
lethe lethe is offline
Authorized User
 
Join Date: Oct 2006
Location: , , .
Posts: 10
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:Originally posted by Jeff Moden
 Lethe,

If you were to post the table schema, some example data (hopefully in the form of inserts), and the offending data, I'd be happy to see why the rollup isn't working...

--Jeff Moden
Hi Jeff-

What does it mean, table schema? You want me to say the name of the columns and their data type? Well, I'm working with an INNER JOIN of 3 or 4 pretty big tables, but I'm really only interested in three or four columns. They are TransactionNumber, Company, Owner, Date. There are a bunch of attributes that I'll to some where clauses with too.

Anyway, the following code seems to do what I want:

Code:
SELECT CASE WHEN (GROUPING(Company) = 1) THEN 'Company Total' ELSE Company END, 
COUNT(CASE WHEN DATEPART(month, Date) = 1 THEN TransactionNumber ELSE NULL END) AS 'Jan 2006',
.
.
.
COUNT(TransactionNumber) AS YearTotal
FROM  aTable
WHERE (Date >= '01/01/2006') AND (Date <= '12/31/2006')
GROUP BY Company WITH ROLLUP
I seem to have learned that WITH ROLLUP only works if your SELECT columns contain some GROUPING clauses, which is why I got syntax errors.

What do you think, is there a reason to prefer your code to mine?

Also, I don't like the way my YearTotal column works. I mean, it gives the correct number, but it seems like it would be logically preferable to tell this column to simply sum the values in the current row under the previous 12 columns. This is how you do things in Excel, for example. It has the benefit that if you change the months, the sum changes automatically. Can I do the same thing in SQL?
Reply With Quote