Using Group by to sum the int value and string

I want to select the Column of Uptime for 8 hour shift and when there is no production I wanted to show "NoProduction" on that select column.

For Example Raw Table look like this

Uptime(hr) Production_Day(0 or 1) t_stamp(date and time) Shift
1 1 Feb 15 05:00:00 EST 2021 Mor
1 1 Feb 15 06:00:00 EST 2021 Mor
---(so on up 13:00 and value of uptime for each hour is 1 and Production_day is 1)
1 1 Feb 15 13:00:00 EST 2021 Mor
0 0 Feb 15 14:00:00 EST 2021 Aft
---(so on up to 21:00 and value of uptime for each hour is 0 and Production_day is 0)
0 0 Feb 15 21:00:00 EST 2021 Aft

And I want to have result like this

Uptime Shift
8 Mor
"NoProduction" Aft

Here is my sql query which I tried,

select SUM(case when Production_Day = 1 then Pastry1_1hrUpTime_Minutes else "NoProduction" end) as Uptime,  Max(Shift) as Shift
FROM Table
WHERE t_stamp >= FORMAT(getDate()-0,'yyyy-MM-dd 05:00:00') and t_stamp <= FORMAT(getDate()-0,'yyyy-MM-dd 21:00:00')
GROUP BY DATEADD(hh, ((DATEDIFF(hh, 0, t_stamp)+3)/8*8)-4, 0)

So anybody can help me, how to use sum int and string in same select statement.

Thanks

1 answer

  • answered 2021-07-28 14:33 Serg

    Provided Pastry1_1hrUpTime_Minutes is int, zero sum value will be casted to '0'. Try

    select case when Uptime ='0' then 'NoProduction' else Uptime end Uptime
        , -- more columns
    from (
          select cast(SUM(case when Production_Day = 1 then Pastry1_1hrUpTime_Minutes else 0 end) as varchar(254)) as Uptime
          , -- more columns
          from ..
    ) t;
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum