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

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%

Try this (I'm guessing SignalDATE is located in table BinarySignals):

``````SELECT DATEPART(yy,s.SignalDATE) AS year
,DATEPART(wk,s.SignalDATE) as Weeks
,CASE WHEN r.Result='Won' THEN 1 ELSE 0 END AS Won
,CASE WHEN r.Result='Lost' THEN 1 ELSE 0 END AS Lost
FROM BinarySignals AS s
INNER JOIN Assets AS a
ON a.AssetId=s.AssetId
INNER JOIN Results AS r
ON r.ResultId=s.ResultId
GROUP BY DATEPART(yy,s.SignalDATE)
,DATEPART(wk,s.SignalDATE)
ORDER BY DATEPART(yy,s.SignalDATE)
,DATEPART(wk,s.SignalDATE)
;
``````
1 Like

Hi

Is this the solution you are looking for ?

Please let me know .. Thanks

create data script

drop table harish_temp
go

create table harish_temp
(
year int null,
weeks int null,
won int null,
Lost int null
)
go

INSERT INTO harish_temp VALUES (2017,48,4,null)
INSERT INTO harish_temp VALUES (2017,49,null,2)
INSERT INTO harish_temp VALUES (2017,49,7,NULL)
INSERT INTO harish_temp VALUES (2017,50,NULL,8)
INSERT INTO harish_temp VALUES (2017,50,19,NULL)
go

My Solution

SELECT year,
weeks,
Min(won)
AS won,
Isnull(Min(lost), 0)
AS lost,
Min(won) + Isnull(Min(lost), 0)
AS total,
Cast(( Min(won) * 1.0 / ( Min(won) + Isnull(Min(lost), 0) ) ) * 100 AS
INT) AS
AVG
FROM harish_temp
GROUP BY year,
weeks

``````SELECT DATEPART(yy,SignalDATE) AS year,DATEPART( wk, Signaldate) as Weeks,
SUM(CASE WHEN Result='Won' THEN 1 ELSE 0 END) AS Won,
SUM(CASE WHEN Result='Lost' THEN 1 ELSE 0 END) AS Lost,
SUM(1) AS Total,
CAST(SUM(CASE WHEN Result='Won' THEN 1 ELSE 0 END) * 100.0 / SUM(1) AS decimal(4, 1)) AS Avg
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)``````