Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Summing SQL SUM's


Summing SQL SUM's

Author
Message
tod.novak
tod.novak
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 164
This is probably super easy, but I just can't quite get it.

I'd like to sum the sums of a result.

Here's what I have so far...


USE <db_name>
GO

SELECT ComputerName AS 'Computer Name',
SUM(BlackWhiteTotalCount) AS 'Total B&W Pages',
SUM(FullColorTotalCount) AS 'Total Color Pages',
Name AS 'Printer Name'
FROM [dbo].[db_name]
WHERE ComputerName LIKE 'ComputerName%' --There are 2 computer names that I'm looking at
GROUP BY ComputerName, Name
GO



I get the result like this:

ComputerName | Total B&W Pages | Total Color Pages | Printer Name |
Computer 1 | 16727 | 5042 | PrinterName |
Computer 2 | 1190 | 2234 | PrinterName |
----------------------------------------------------------------------------------

This is fine, but I'd really like to total the B&W Pages column, and the Color Pages column.

Like I said, this is probably ridiculously easy, but I just can't figure it out. Any help is greatly appreciated.
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14973 Visits: 38991
you could simply sum the two together as a third column, i'd think:

SELECT ComputerName AS 'Computer Name',
SUM(BlackWhiteTotalCount) AS 'Total B&W Pages',
SUM(FullColorTotalCount) AS 'Total Color Pages',
SUM(BlackWhiteTotalCount) + SUM(FullColorTotalCount) AS 'Total of All Pages',
Name AS 'Printer Name'
FROM [dbo].[db_name]
WHERE ComputerName LIKE 'ComputerName%' --There are 2 computer names that I'm looking at
GROUP BY ComputerName, Name



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mickyT
mickyT
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 3309
Hi

Have a look at ROLLUP, I think this will do what you want.
SELECT   ComputerName AS 'Computer Name', 
SUM(BlackWhiteTotalCount) AS 'Total B&W Pages',
SUM(FullColorTotalCount) AS 'Total Color Pages',
Name AS 'Printer Name'
FROM [dbo].[db_name]
WHERE ComputerName LIKE 'ComputerName%' --There are 2 computer names that I'm looking at
GROUP BY ROLLUP (ComputerName, Name)


a4apple
a4apple
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 406
:-P

You have all the answer in your code...


SELECT ComputerName AS [Computer Name]
, [Name] AS [Printer Name]
, SUM(BlackWhiteTotalCount) AS [Total B&W Pages]
, SUM(FullColorTotalCount) AS [Total Color Pages]
, SUM(BlackWhiteTotalCount) + SUM(FullColorTotalCount) AS [Total]
FROM [dbo].[db_name]
WHERE ComputerName LIKE 'ComputerName%'
GROUP BY [ComputerName], [Name]



Just add the two sums to get the final total...

Good Luck Smile .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
tod.novak
tod.novak
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 164
Thanks for all the replies! These are getting me the totals for b&w pages and color pages combined, but what I need are the totals for b&w, and the totals for color. Totaling down the rows, instead of across the columns.

Hopefully the attached will make sense.
Attachments
results.png (7 views, 3.00 KB)
mickyT
mickyT
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 3309
Have you tried the ROLLUP? I have altered your original slightly to match the result set you wanted (without the printer name) and it should provide the result you want
SELECT   COALESCE(ComputerName,'TOTAL') AS 'Computer Name', 
SUM(BlackWhiteTotalCount) AS 'Total B&W Pages',
SUM(FullColorTotalCount) AS 'Total Color Pages'
FROM [db_name]
WHERE ComputerName LIKE 'ComputerName%' --There are 2 computer names that I'm looking at
GROUP BY ROLLUP (ComputerName)


a4apple
a4apple
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 Visits: 406
@OP,

1) If you want to show just the totals for both of them, Use a Rollup. Without roll up, what happens is you cannot use the Group by anymore since you are dealing with two different computer names.
2) If you are planning to display this using an SSRS report, it will do the task automatically for you.

Good Luck Smile .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
tod.novak
tod.novak
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 164
mickyT (4/10/2014)
Have you tried the ROLLUP? I have altered your original slightly to match the result set you wanted (without the printer name) and it should provide the result you want
SELECT   COALESCE(ComputerName,'TOTAL') AS 'Computer Name', 
SUM(BlackWhiteTotalCount) AS 'Total B&W Pages',
SUM(FullColorTotalCount) AS 'Total Color Pages'
FROM [db_name]
WHERE ComputerName LIKE 'ComputerName%' --There are 2 computer names that I'm looking at
GROUP BY ROLLUP (ComputerName)




mickyT,
This worked perfectly! Thank you so much for the help. I'm going to look up COALESCE to help me understand how it worked.

Thank you everyone for the replies. I appreciate all the quick responses!
mickyT
mickyT
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 3309
tod.novak (4/10/2014)

mickyT,
This worked perfectly! Thank you so much for the help. I'm going to look up COALESCE to help me understand how it worked.

Thank you everyone for the replies. I appreciate all the quick responses!

COALESCE is used to return the first non null value from the list of parameters. ISNULL could also have been used in this situation, but as todays question of the day demonstrated be careful with datatypes.

This sort of shows what the different options do
WITH testData AS (
SELECT *
FROM (VALUES
(CAST('Grp A' AS VARCHAR(5)), 2, 1),
(CAST('Grp A' AS VARCHAR(5)), 2, 2),
(CAST('Grp A' AS VARCHAR(5)), 2, 3),
(CAST('Grp A' AS VARCHAR(5)), 2, 4),
(CAST('Grp B' AS VARCHAR(5)), 4, 1),
(CAST('Grp B' AS VARCHAR(5)), 4, 2),
(CAST('Grp B' AS VARCHAR(5)), 4, 3),
(CAST('Grp B' AS VARCHAR(5)), 4, 4)
) AS TD(Name, Value, Seq)
)
SELECT Name UntouchedName,
COALESCE(Name, 'Grand Total') CoalescedName,
ISNULL(Name, 'Grand Total') IsNullName,
SUM(Value) TotalValue
FROM testData
GROUP BY ROLLUP (Name);



UntouchedName CoalescedName IsNullName TotalValue
------------- ------------- ---------- -----------
Grp A Grp A Grp A 8
Grp B Grp B Grp B 16
NULL Grand Total Grand 24

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search