Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Summing SQL SUM's Expand / Collapse
Author
Message
Posted Thursday, April 10, 2014 12:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 5:20 PM
Points: 6, Visits: 85
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.
Post #1560583
Posted Thursday, April 10, 2014 1:17 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:06 AM
Points: 12,923, Visits: 32,280
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1560595
Posted Thursday, April 10, 2014 1:19 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:45 PM
Points: 1,080, Visits: 3,170
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)

Post #1560596
Posted Thursday, April 10, 2014 1:19 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 5:53 PM
Points: 143, Visits: 316


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.
Post #1560597
Posted Thursday, April 10, 2014 1:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 5:20 PM
Points: 6, Visits: 85
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.



  Post Attachments 
results.png (7 views, 3.19 KB)
Post #1560614
Posted Thursday, April 10, 2014 2:07 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:45 PM
Points: 1,080, Visits: 3,170
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)

Post #1560618
Posted Thursday, April 10, 2014 2:28 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 5:53 PM
Points: 143, Visits: 316
@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.
Post #1560626
Posted Thursday, April 10, 2014 3:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 5:20 PM
Points: 6, Visits: 85
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!
Post #1560647
Posted Thursday, April 10, 2014 4:07 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:45 PM
Points: 1,080, Visits: 3,170
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
Post #1560658
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse