Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 adding the sum from 2 tables

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 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

Running the stored proc gives me this result set:

InternetNumber Trancount
9531 2188
9532 8
9534 22
9534 644
9531 175

Now I want to take it a step further and total the TranCount by InternetNumber. The result set I'd get should be:

9531 2363
9532 8
9534 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
Go to Top of Page

PeterG
Posting Yak Master

156 Posts

Posted - 2004-04-22 : 18:06:05
Thanks Tara, but I get "Incorrect syntax near the keyword 'GROUP'."
Go to Top of Page

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
) A
GROUP BY A.InternetNum
Go to Top of Page

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 Amount
9531 9000647 4185.300810
9532 8 3000.000000
9534 666 3000.000000

A 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 Amount
FROM
(
SELECT InternetNum, SUM(TransactionsCount) as TranCount
FROM dbo.DownloadHistory
WHERE DATEPART(mm,DateDownloaded) = @month AND DATEPART(yy,DateDownloaded) = @year
GROUP BY InternetNum
UNION
SELECT InternetNum, SUM(TransactionsCount) as TranCount
FROM dbo.XMLDownloadArchive
WHERE DATEPART(mm,DateDownloaded) = @month AND DATEPART(yy,DateDownloaded) = @year AND UserID IS NOT NULL
GROUP BY InternetNum
) A
GROUP BY A.InternetNum
ORDER BY A.InternetNum
Go to Top of Page

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 ROLLUP

I'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
Go to Top of Page

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?
Go to Top of Page

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 exceeded
THEN ((SUM(A.TranCount) - 6451613) * .000465) + 3000 --multiply additional tran with the per tran rate
ELSE 3000 END AS Amount
FROM
(
SELECT InternetNum, SUM(TransactionsCount) as TranCount
FROM dbo.DownloadHistory
WHERE DATEPART(mm,DateDownloaded) = @month AND DATEPART(yy,DateDownloaded) = @year
GROUP BY InternetNum
UNION
SELECT InternetNum, SUM(TransactionsCount) as TranCount
FROM dbo.XMLDownloadArchive
WHERE DATEPART(mm,DateDownloaded) = @month AND DATEPART(yy,DateDownloaded) = @year AND UserID IS NOT NULL
GROUP BY InternetNum
) A
GROUP BY A.InternetNum
ORDER BY A.InternetNum
)

Tara
Go to Top of Page

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.
Go to Top of Page

Lewie
Starting Member

42 Posts

Posted - 2004-04-24 : 03:18:06
You may also want to change your UNION to a UNION ALL
Go to Top of Page
   

- Advertisement -