Solved

# Handle a SUM and COUNT through 3 GROUPINGS.

Posted on 2013-11-07
396 Views
Can you suggest if the below can be bettered or simplified.. the logic is as below:

The first 3 queries are put into 3 temp tables A B and C. The only difference between the 3 queries are in the HAVING clause.

Because we are doing a SUM on Col10, I was not able to do a COUNT on another column (Col0) on the same query.

So I am doing my count on Col3, in three seperate queries from A, B, C temp tables - doing a UNION on them, and then grouping them again with 3 SUM operations.

thanks-
0
Question by:25112
• 4

LVL 5

Author Comment

SELECT COL0 GroupColumn, COL1 FirstColumn, COl2 SecondColumn, COL3 ThirdColumn, COL4 FourthColumn, (COL5+COL6-COL7+COL8-COL9) FifthColumn
into A
from
JOIN & Conditions
GROUP BY COL0 ,COL1 ,COl2 ,COL3 ,COL4 ,(COL5+COL6-COL7+COL8-COL9)
HAVING sum(COL10)> (COL5+COL6-COL7+COL8-COL9)

------------
SELECT COL0 GroupColumn, COL1 FirstColumn, COl2 SecondColumn, COL3 ThirdColumn, COL4 FourthColumn, (COL5+COL6-COL7+COL8-COL9) FifthColumn
into B
from
JOIN & Conditions
GROUP BY COL0 ,COL1 ,COl2 ,COL3 ,COL4 ,(COL5+COL6-COL7+COL8-COL9)
HAVING sum(COL10)= (COL5+COL6-COL7+COL8-COL9)

---------------
SELECT COL0 GroupColumn, COL1 FirstColumn, COl2 SecondColumn, COL3 ThirdColumn, COL4 FourthColumn, (COL5+COL6-COL7+COL8-COL9) FifthColumn
INTO C
from
JOIN & Conditions
GROUP BY COL0 ,COL1 ,COl2 ,COL3 ,COL4 ,(COL5+COL6-COL7+COL8-COL9)
HAVING sum(COL10)< (COL5+COL6-COL7+COL8-COL9)

---------------

SELECT FirstColumn, SecondColumn, ThirdColumn, FourthColumn, FifthColumn,
SUM(EighthColumn) [6thCol],
SUM(SeventhColumn) [7thCol],
SUM(SixthColumn) [8thCol]
from

(
select FirstColumn, SecondColumn, ThirdColumn, FourthColumn, FifthColumn,  COUNT(COL0) SixthColumn, 0 SeventhColumn, , 0 EighthColumn from A
group by FirstColumn, SecondColumn, ThirdColumn, FourthColumn, FifthColumn

UNION

select FirstColumn, SecondColumn, ThirdColumn, FourthColumn, FifthColumn,  0 SixthColumn, COUNT(COL0) SeventhColumn, , 0 EighthColumn from B
group by FirstColumn, SecondColumn, ThirdColumn, FourthColumn, FifthColumn

UNION

select FirstColumn, SecondColumn, ThirdColumn, FourthColumn, FifthColumn,  0 SixthColumn, 0 SeventhColumn, , COUNT(COL0) EighthColumn from C
group by FirstColumn, SecondColumn, ThirdColumn, FourthColumn, FifthColumn
) A

GROUP BY FirstColumn, SecondColumn, ThirdColumn, FourthColumn, FifthColumn

drop table A
drop table B
drop table C
0

LVL 5

Author Comment

i have edited the above with a CTE so the code is smaller.. can you suggest any improvements/suggestions?

;WITH CTE as
(
select  COL0,COL1 , COL2, COL3, COL4,  ((COL5+  COL6) - ( COL7 + COL8+ COL9)) COL10 ,sum(COL11)  COL11

from

JOIN

ON

where

group by COL0, COL1,COL2,COL4,COL3,((COL5+ COL6) -( COL7 + COL8+ COL9))
)

---------------------

SELECT COL3, COL4,  COL1, COL2, COL10,
SUM(EighthColumn) [6thCol],
SUM(SeventhColumn) [7thCol],
SUM(SixthColumn) [8thCol]
from
(
select COL1, COL2, COL3, COL4, COL10,  COUNT(COL0) SixthColumn, 0 SeventhColumn, 0 EighthColumn from CTE WHERE COL11 > COL10 group by COL1, COL2, COL3, COL4, COL10 union
select COL1, COL2, COL3, COL4, COL10,  0 SixthColumn, COUNT(COL0) SeventhColumn, 0 EighthColumn from CTE WHERE COL11 = COL10 group by COL1, COL2, COL3, COL4, COL10 union
select COL1, COL2, COL3, COL4, COL10,  0 SixthColumn, 0 SeventhColumn, COUNT(COL0) EighthColumn from CTE WHERE COL11 < COL10 group by COL1, COL2, COL3, COL4, COL10
)a
GROUP BY COL1, COL2, COL3, COL4, COL10
ORDER BY COL1
0

LVL 31

Expert Comment

It seems that there should be a simpler way. Can you provide some sample relevant data for your beginning table structures and your expected output withe some description of how your results are to be generated?
0

LVL 5

Author Comment

CREATE TABLE CTE (COL0 INT,COL1 VARCHAR(11) , COL2 INT, COL3 VARCHAR(11), COL4 INT,COL10 SMALLINT,COL11 SMALLINT)

then run this:

SELECT COL3, COL4,  COL1, COL2, COL10,
SUM(EighthColumn) [6thCol],
SUM(SeventhColumn) [7thCol],
SUM(COL0) [8thCol]
from
(
select COL1, COL2, COL3, COL4, COL10,  COUNT(COL0) COL0, 0 SeventhColumn, 0 EighthColumn from CTE WHERE COL11 > COL10 group by COL1, COL2, COL3, COL4, COL10 union
select COL1, COL2, COL3, COL4, COL10,  0 COL0, COUNT(COL0) SeventhColumn, 0 EighthColumn from CTE WHERE COL11 = COL10 group by COL1, COL2, COL3, COL4, COL10 union
select COL1, COL2, COL3, COL4, COL10,  0 COL0, 0 SeventhColumn, COUNT(COL0) EighthColumn from CTE WHERE COL11 < COL10 group by COL1, COL2, COL3, COL4, COL10
)a
GROUP BY COL1, COL2, COL3, COL4, COL10
ORDER BY 3
CTE.csv
0

LVL 48

Accepted Solution

PortletPaul earned 500 total points
It can be done by a single query, just use case expressions inside the COUNT() function
``````SELECT
COL1
, COL2
, COL3
, COL4
, COL10
, count(CASE WHEN COL11 < COL10 THEN COL0 END) [6thCol_11_lt_10]
, count(CASE WHEN COL11 = COL10 THEN COL0 END) [7thCol_10_eq_11]
, count(CASE WHEN COL11 > COL10 THEN COL0 END) [8thCol_11_gt_10]
FROM CTE
GROUP BY
COL1
, COL2
, COL3
, COL4
, COL10
ORDER BY
COL1
;
``````
as an additional note, if you do ever need to use multiple queries like you have been doing so far AND you are summing those values do NOT use "UNION"

You should have been using UNION ALL

UNION by itself can make rows 'disappear' and your sum() could be wrong

UNION ALL does not make rows disappear, and it is faster too.
0

LVL 5

Author Comment

nice!thx
0

## Featured Post

### Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…