Author |
Topic |
PeterG
Posting Yak Master
156 Posts |
Posted - 2004-04-22 : 17:48:09
|
I have the following stored proc:SELECT InternetNum, SUM(TransactionsCount) as TranCountFROM dbo.DownloadHistory WHERE DATENAME(mm,DateDownloaded) = 'April' AND DATEPART(yy,DateDownloaded) = '2004'GROUP BY InternetNumUNIONSELECT InternetNum, SUM(TransactionsCount) as TranCountFROM dbo.DownloadArchiveWHERE DATENAME(mm,DateDownloaded) = 'April' AND DATEPART(yy,DateDownloaded) = '2004' AND UserID IS NOT NULLGROUP BY InternetNumRunning the stored proc gives me this result set:InternetNumber Trancount9531 21889532 89534 229534 6449531 175Now I want to take it a step further and total the TranCount by InternetNumber. The result set I'd get should be:9531 23639532 89534 666 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-22 : 17:54:52
|
[code]SELECT InternetNum, SUM(TranCount)FROM ( SELECT InternetNum, SUM(TransactionsCount) as TranCount FROM dbo.DownloadHistory WHERE DATENAME(mm,DateDownloaded) = 'April' AND DATEPART(yy,DateDownloaded) = '2004' GROUP BY InternetNum UNION SELECT InternetNum, SUM(TransactionsCount) as TranCount FROM dbo.DownloadArchive WHERE DATENAME(mm,DateDownloaded) = 'April' AND DATEPART(yy,DateDownloaded) = '2004' AND UserID IS NOT NULL GROUP BY InternetNum )GROUP BY InternetNum[/code]Tara |
|
|
PeterG
Posting Yak Master
156 Posts |
Posted - 2004-04-22 : 18:06:05
|
Thanks Tara, but I get "Incorrect syntax near the keyword 'GROUP'." |
|
|
PeterG
Posting Yak Master
156 Posts |
Posted - 2004-04-22 : 18:09:32
|
I got it.here's the working stored proc:SELECT A.InternetNum, SUM(A.TranCount)FROM ( SELECT InternetNum, SUM(TransactionsCount) as TranCount FROM dbo.XMLDownloadHistory WHERE DATENAME(mm,DateDownloaded) = 'April' AND DATEPART(yy,DateDownloaded) = '2004' GROUP BY InternetNum UNION SELECT InternetNum, SUM(TransactionsCount) as TranCount FROM dbo.XMLDownloadArchive WHERE DATENAME(mm,DateDownloaded) = 'April' AND DATEPART(yy,DateDownloaded) = '2004' AND UserID IS NOT NULL GROUP BY InternetNum ) AGROUP BY A.InternetNum |
|
|
PeterG
Posting Yak Master
156 Posts |
Posted - 2004-04-23 : 17:33:55
|
Here's another twist to this stored proc.I want to total the Amount calculated. The result set after running the proc is:InternetNumber TotalTransactions Amount9531 9000647 4185.3008109532 8 3000.0000009534 666 3000.000000A TotalAmount filed should be 10185.300810. I tried adding COMPUTE SUM(Amount) at the end of the proc, but I get an invalid column 'Amount' error.SELECT A.InternetNum as [InternetNumber], SUM(A.TranCount) as [TotalTransactions], CASE WHEN SUM(A.TranCount) > 6451613 --max transaction exceeded THEN ((SUM(A.TranCount) - 6451613) * .000465) + 3000 --multiply additional tran with the per tran rate ELSE 3000 END AS AmountFROM (SELECT InternetNum, SUM(TransactionsCount) as TranCountFROM dbo.DownloadHistory WHERE DATEPART(mm,DateDownloaded) = @month AND DATEPART(yy,DateDownloaded) = @yearGROUP BY InternetNumUNIONSELECT InternetNum, SUM(TransactionsCount) as TranCountFROM dbo.XMLDownloadArchiveWHERE DATEPART(mm,DateDownloaded) = @month AND DATEPART(yy,DateDownloaded) = @year AND UserID IS NOT NULLGROUP BY InternetNum) AGROUP BY A.InternetNumORDER BY A.InternetNum |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-23 : 17:36:05
|
I think this is when you use the ROLLUP operator. GROUP BY A.InternetNum WITH ROLLUPI've never used it before, but I'm pretty sure that this is what it is for. Take a look at the example in BOL for ROLLUP operator.Tara |
|
|
PeterG
Posting Yak Master
156 Posts |
Posted - 2004-04-23 : 17:49:58
|
I tried it, but ROLLUP doesn't give me the value I'm expecting. I read up on COMPUTE, but I think it's because I am calculating the value of the Amount field that somehow messes up the query? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-04-23 : 17:56:39
|
Do you want the total in the same result set? If not:SELECT SUM(TotalTransactions)FROM(SELECT A.InternetNum as [InternetNumber], SUM(A.TranCount) as [TotalTransactions],CASE WHEN SUM(A.TranCount) > 6451613 --max transaction exceededTHEN ((SUM(A.TranCount) - 6451613) * .000465) + 3000 --multiply additional tran with the per tran rateELSE 3000 END AS AmountFROM (SELECT InternetNum, SUM(TransactionsCount) as TranCountFROM dbo.DownloadHistory WHERE DATEPART(mm,DateDownloaded) = @month AND DATEPART(yy,DateDownloaded) = @yearGROUP BY InternetNumUNIONSELECT InternetNum, SUM(TransactionsCount) as TranCountFROM dbo.XMLDownloadArchiveWHERE DATEPART(mm,DateDownloaded) = @month AND DATEPART(yy,DateDownloaded) = @year AND UserID IS NOT NULLGROUP BY InternetNum) AGROUP BY A.InternetNumORDER BY A.InternetNum)Tara |
|
|
PeterG
Posting Yak Master
156 Posts |
Posted - 2004-04-23 : 18:01:08
|
Yea, I've thought about that but I want the total amount in the same resultset. |
|
|
Lewie
Starting Member
42 Posts |
Posted - 2004-04-24 : 03:18:06
|
You may also want to change your UNION to a UNION ALL |
|
|
|