GROUP BY TO appear IN one row THEN Calculate total and AVG

Question

GROUP BY TO appear IN one row THEN Calculate total and AVG

Last

Old Hand

Points: 331
More actions
February 11, 2018 at 11:41 am

#343596

Hi
I currently when i run my query i get the following output.
**Current**
|year|Weeks|Won|Lost|
|---|---|---|---|
|2017|48|4|NULL|
|2017|49|NULL|2|
|2017|49|7|NULL|
|2017|50|NULL|8|
|2017|50|19|NULL
As you can see,the WON and LOST result of the same week falls on different rows and I would like them to appear in one row and have and additional column for total for that week than an average column of WON/Total
**Query**
SELECT DATEPART(yy,SignalDATE) AS year,DATEPART( wk, Signaldate) as Weeks,
    CASE
     WHEN Result='Won' THEN
     count(result)
     END AS Won,
         CASE
     WHEN Result='Lost' THEN
     count(result)

     END AS Lost
  FROM [BinarySignals]
     JOIN Assets ON Assets.AssetId=BinarySignals.AssetId
     JOIN Results ON Results.ResultId=BinarySignals.ResultId
GROUP BY DATEPART( wk, Signaldate),DATEPART(yy,SignalDATE),result
ORDER BY DATEPART(yy,SignalDATE),DATEPART( wk, Signaldate)
**Desired:**
**Won/Lost to appear in one row if they are in the same week, add total column then divide Won by total to get percentage**
|year|Weeks|Won|Lost|Total|AVG
|---|---|---|---|---|---|
|2017|48|4|0|4|100%
|2017|49|7|2|9|77%
|2017|50|19|8|27|70.3%

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

What you're looking for is called a "Crosstab". The following is your code modified to do a "Crosstab".

SELECT Year = DATEPART(yy,SignalDATE) ,Weeks = DATEPART(wk,Signaldate) ,Won = SUM(CASE WHEN Result='Won' THEN 1 ELSE 0 END) ,Lost = SUM(CASE WHEN Result='Lost' THEN 1 ELSE 0 END) FROM dbo.BinarySignals JOIN dbo.Assets ON Assets.AssetId = BinarySignals.AssetId JOIN dbo.Results ON Results.ResultId = BinarySignals.ResultId GROUP BY DATEPART(wk,Signaldate), DATEPART(yy,SignalDATE) ORDER BY Year, Weeks ;

I'd have added table aliases to make future troubleshooting better but, much like a future troubleshooter, I know for sure which columns go with which tables. I also strongly recommend that you use 2 part naming for all joined queries.

Some will provide the same code using the PIVOT operator. I prefer to not use it because it's a bit limited for other things, like a row total column and is usually slower than a Crosstab. Please see the following article for more information.
http://www.sqlservercentral.com/articles/T-SQL/63681/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Last Old Hand Points: 331 More actions · Answer 2

Thank you.That managed to resolve the bulk of the issue and by doing a CTE I managed to get the AVG

WITH OS AS(SELECT Year = DATEPART(yy,SignalDATE) ,Weeks = DATEPART(wk,Signaldate) ,Won = SUM(CASE WHEN Result='Won' THEN 1 ELSE 0 END) ,Lost = SUM(CASE WHEN Result='Lost' THEN 1 ELSE 0 END) ,Total = SUM(CASE WHEN Result='Lost' OR Result='Won' THEN 1 ELSE 0 END) FROM dbo.BinarySignals JOIN dbo.Assets ON Assets.AssetId = BinarySignals.AssetId JOIN dbo.Results ON Results.ResultId = BinarySignals.ResultId GROUP BY DATEPART(wk,Signaldate), DATEPART(yy,SignalDATE)

) SELECT YEAR,WEEKS,WON,LOST, TOTAL,ROUND((CAST(WON AS FLOAT)/TOTAL*100),2) AS 'AVG.%' FROM OS GROUP BY YEAR,WEEKS,WON,LOST,TOTAL ORDER BY Year DESC,WEEKS DESC

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

There's no need for the second GROUP BY and you can simplify the average calculation. Also, consider adopting and capitalization and indention standard for readability. You should also start using semi-colons properly because not using them has been deprecated and someday you're going to be busy adding them all in the right places if you don't start now. Last, but not least, you should get out of the habit of using single quoted strings for odd column names and use brackets, instead, so the column names don't just look like strings when you're in a hurry for troubleshooting.

WITH OS AS ( SELECT Year = DATEPART(yy,SignalDATE) ,Weeks = DATEPART(wk,Signaldate) ,Won = SUM(CASE WHEN Result='Won' THEN 1 ELSE 0 END) ,Lost = SUM(CASE WHEN Result='Lost' THEN 1 ELSE 0 END) ,Total = SUM(CASE WHEN Result='Lost' OR Result='Won' THEN 1 ELSE 0 END) FROM dbo.BinarySignals JOIN dbo.Assets ON Assets.AssetId = BinarySignals.AssetId JOIN dbo.Results ON Results.ResultId = BinarySignals.ResultId GROUP BY DATEPART(wk,Signaldate), DATEPART(yy,SignalDATE) ) SELECT Year,Weeks,Won,Lost,Total ,[AVG.%] = ROUND(Won*100.0/Total,2) FROM OS ORDER BY Year DESC, Weeks DESC ;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)