Solved

Handle a SUM and COUNT through 3 GROUPINGS.

Posted on 2013-11-07
6
400 Views
Last Modified: 2013-11-09
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
Comment
Question by:25112
  • 4
6 Comments
 
LVL 5

Author Comment

by:25112
ID: 39630787
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

by:25112
ID: 39631164
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 32

Expert Comment

by:awking00
ID: 39631170
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 5

Author Comment

by:25112
ID: 39631343
king, please create this table..

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

then upload the attached csv to it please using Export/Import.

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

by:
PortletPaul earned 500 total points
ID: 39632793
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
;

Open in new window

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

by:25112
ID: 39635770
nice!thx
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now