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

1

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%
2

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
3

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

4 
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)