Group by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Group by

Author

Topic

mukhan85
Starting Member

46 Posts

Posted - 2008-06-16 : 18:54:04

Hi folks! I have a problem. Here is my query. I try to explain what it does. It makes LEFT OUTER JOIN on two views.

The result of the query is given below:

SELECT sub1.metric_value, sub2.am_value1,
sub2.am_value2,
sub2.am_value3
FROM
(
SELECT 'Successful' AS metric_value union
SELECT 'Successful with Issues' AS metric_value union
SELECT 'Unsuccessful' AS metric_value union
SELECT 'Backed Out' AS metric_value union
SELECT 'No Closure Stat' AS metric_value
)

AS sub1 LEFT OUTER JOIN (SELECT
(CASE Status_Reason
WHEN 5000 THEN 'Successful'
WHEN 6000 THEN 'Successful with Issues'
WHEN 7000 THEN 'Unsuccessful'
WHEN 8000 THEN 'Backed Out'
ELSE 'No Closure Stat'
end) AS metric_value,

/*ATS*/
SUM(CASE WHEN Product_Cat_Tier_3__2_ LIKE '%Sector%' THEN 1 ELSE 0 end) AS am_value1,
/*ARCA*/
SUM(CASE WHEN (Product_Cat_Tier_3__2_ LIKE '%NYSE Trading%' AND Location_Company = 'ARCA' )THEN 1 ELSE 0 end) AS am_value2,
/*NMS*/
SUM(CASE WHEN Product_Cat_Tier_3__2_ LIKE 'NMS' THEN 1 ELSE 0 end) AS am_value3

FROM Change

WHERE
YEAR(DATEADD(second, Submit_Date, '1969-12-31 8:00:00 PM')) = YEAR(GETDATE()) AND

Product_Cat_Tier_1_2_ = 'Network' AND
Product_Cat_Tier_2__2_ = 'RFC' AND Change_Request_Status = 11 AND
Product_Cat_Tier_3__2_ NOT IN ('Installation', 'Voice AND Video')
GROUP BY (CASE Status_Reason
WHEN 5000 THEN 'Successful'
WHEN 6000 THEN 'Successful with Issues'
WHEN 7000 THEN 'Unsuccessful'
WHEN 8000 THEN 'Backed Out'
ELSE 'No Closure Stat'
end)
) AS sub2 ON (sub1.metric_value = sub2.metric_value)

===================================================
RESULT
metric_value am_value1 am_value2 am_value3
------------ ----------- ----------- -----------
Backed Out NULL NULL NULL
No Closure Stat 0 16 2
Successful 3 201 4
Successful with Issues 0 0 0
Unsuccessful NULL NULL NULL

===================================================
NOw I have modified my query a little bit. MOdified sections are in RED:
SELECT sub1.metric_value, sub2.am_value1,
sub2.am_value2,
sub2.am_value3
FROM
(
SELECT 'Successful' AS metric_value union
SELECT 'Successful with Issues' AS metric_value union
SELECT 'Unsuccessful' AS metric_value union
SELECT 'Backed Out' AS metric_value union
SELECT 'No Closure Stat' AS metric_value
)

AS sub1 LEFT OUTER JOIN (SELECT
(CASE Status_Reason
WHEN 5000 THEN 'Successful'
WHEN 6000 THEN 'Successful with Issues'
WHEN 7000 THEN 'Unsuccessful'
WHEN 8000 THEN 'Backed Out'
ELSE 'No Closure Stat'
end) AS metric_value,

/*ATS*/
SUM(CASE WHEN Product_Cat_Tier_3__2_ LIKE '%Sector%' THEN 1 ELSE 0 end) AS am_value1,
/*ARCA*/
SUM(CASE WHEN (Product_Cat_Tier_3__2_ LIKE '%NYSE Trading%' AND Location_Company = 'ARCA' )THEN 1 ELSE 0 end) AS am_value2,
/*NMS*/
SUM(CASE WHEN Product_Cat_Tier_3__2_ LIKE 'NMS' THEN 1 ELSE 0 end) AS am_value3,

month(DATEADD(second, Submit_Date, '1969-12-31 8:00:00 PM')) as myMOnth
FROM Change

WHERE
YEAR(DATEADD(second, Submit_Date, '1969-12-31 8:00:00 PM')) = YEAR(GETDATE()) AND

Product_Cat_Tier_1_2_ = 'Network' AND
Product_Cat_Tier_2__2_ = 'RFC' AND Change_Request_Status = 11 AND
Product_Cat_Tier_3__2_ NOT IN ('Installation', 'Voice AND Video')
GROUP BY (CASE Status_Reason
WHEN 5000 THEN 'Successful'
WHEN 6000 THEN 'Successful with Issues'
WHEN 7000 THEN 'Unsuccessful'
WHEN 8000 THEN 'Backed Out'
ELSE 'No Closure Stat'
end) , YEAR(DATEADD(second, Submit_Date, '1969-12-31 8:00:00 PM')) , month(DATEADD(second, Submit_Date, '1969-12-31 8:00:00 PM'))
) AS sub2 ON (sub1.metric_value = sub2.metric_value)
==============================
Here is the result of the query:
metric_value am_value1 am_value2 am_value3
------------ ----------- ----------- -----------
Backed Out NULL NULL NULL
No Closure Stat 0 4 0
No Closure Stat 0 1 0
No Closure Stat 0 2 1
No Closure Stat 0 4 1
No Closure Stat 0 4 0
No Closure Stat 0 1 0
Successful 1 41 4
Successful 0 32 0
Successful 0 18 0
Successful 1 37 0
Successful 1 54 0
Successful 0 19 0
Successful with Issues 0 0 0
Successful with Issues 0 0 0
Unsuccessful NULL NULL NULL

As you can see there are more than 5 rows, as was at the first result set. I need 5 rows as I got before I modified the query, At the same time I need to select MONTH also. Is there any way to do that? THank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-17 : 00:14:29

Didnt understand how you can show months even after retaining your first result set. your first result set just sums the figures based on metric_value alone. In the second query you're grouping it also by year,month so that you get summary of metric_value per month of each year involved. If you need to show the month wise data you really need to repeat the metric_value once for each month.In other words if you want to group by metric_value alone what month value will you show against the summary figure which is aggregate for all the months? that doesnt obviously make sense. And if your intention is to show all the lines but show metric_value only once without repeating as below

No Closure Stat Jan 0 4 0
Feb 0 1 0
Mar 0 2 1...

Then this is something that you should try to do at your front end application as its a display formatting issue.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources