April 10, 2014 at 12:45 pm
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
SELECTComputerName 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.
April 10, 2014 at 1:17 pm
you could simply sum the two together as a third column, i'd think:
SELECTComputerName 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
April 10, 2014 at 1:19 pm
Hi
Have a look at ROLLUP, I think this will do what you want.
SELECTComputerName 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)
April 10, 2014 at 1:19 pm
😛
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 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
April 10, 2014 at 1:54 pm
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.
April 10, 2014 at 2:07 pm
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
SELECTCOALESCE(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)
April 10, 2014 at 2:28 pm
@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 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
April 10, 2014 at 3:35 pm
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
SELECTCOALESCE(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!
April 10, 2014 at 4:07 pm
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply