Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.
COURSE of the MONTH
8 days, 12 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Handle a SUM and COUNT through 3 GROUPINGS.
Posted on 2013-11-07
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-
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-
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
6 Comments
SELECT COL0 GroupColumn, COL1 FirstColumn, COl2 SecondColumn, COL3 ThirdColumn, COL4 FourthColumn, (COL5+COL6-COL7+COL8-COL9)
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)
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)
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
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)
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)
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
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
)
---------------------
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
;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
)
---------------------
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
Active today
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?
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
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
Active today
It can be done by a single query, just use case expressions inside the COUNT() function
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.
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
;
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.
Featured Post
In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on.
…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied?
You can use SQL Server Initialize from Backup…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB.
To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month8 days, 12 hours left to enroll
764 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.