morning daily and night calculation

Hi
please have a look to my query
$result=mysql_query( "SELECT a.hr, 
      SUM(case when b.pulse_channel = 1 then b.unit_qty else 0 end) as name1,
      SUM(case when b.pulse_channel = 2 then b.unit_qty else 0 end) as name2,
      SUM(case when b.pulse_channel = 3 then b.unit_qty else 0 end) as name3,
      SUM(case when b.pulse_channel = 4 then b.unit_qty else 0 end) as name4,
      SUM(b.unit_qty) as Total
from (
	select 0  as hr 
	union select 1
	union select 2 
	union select 3
	union select 4 
	union select 5 
	union select 6
	union select 7
	union select 8
	union select 9
	union select 10
	union select 11
	union select 12
	union select 13
	union select 14
	union select 15
	union select 16
	union select 17
	union select 18
	union select 19
	union select 20
	union select 21
	union select 22
	union select 23) a 
left join count_transactions b on a.hr = Hour(b.tran_date)
WHERE DATE(b.tran_date) BETWEEN '$dateb' AND '$dateb' AND unit_serial=$netw
GROUP BY a.hr" ) or die("SELECT Error: ".mysql_error());

Open in new window

how i can calculate unit_qty for morning (from 0 to 6) then daily from 7pm to 19:59 then night from 20 to 23:59pm
i hope you inderstand my query
asaidiAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
johanntagleConnect With a Mentor Commented:
it has to be group by case ..... end not group by part_of_day
0
 
johanntagleCommented:
change the "a.hr" in the select to:

case when a.hr between 0 and 6 then 'morning'
        when a.hr between 7 and 19 then 'daily'
        when a.hr between 20 and 23 then 'night'
end as part_of_day

Also change it in the group by, except without the "as part_of_day" of course.
0
 
asaidiAuthor Commented:
Hi
thanks for your answer
is this correct:i cannot get in my html table rowe from 0 to 23 please check a picture of my report
$result=mysql_query( "SELECT 
                    case when a.hr between 0 and 6 then 'morning'
                         when a.hr between 6 and 19 then 'daily'
                         when a.hr between 19 and 23 then 'night'
                         end as part_of_day,
              
      SUM(case when b.pulse_channel = 1 then b.unit_qty else 0 end) as name1,
      SUM(case when b.pulse_channel = 2 then b.unit_qty else 0 end) as name2,
      SUM(case when b.pulse_channel = 3 then b.unit_qty else 0 end) as name3,
      SUM(case when b.pulse_channel = 4 then b.unit_qty else 0 end) as name4,
      SUM(b.unit_qty) as Total
from (
	select 0  as hr 
	union select 1
	union select 2 
	union select 3
	union select 4 
	union select 5 
	union select 6
	union select 7
	union select 8
	union select 9
	union select 10
	union select 11
	union select 12
	union select 13
	union select 14
	union select 15
	union select 16
	union select 17
	union select 18
	union select 19
	union select 20
	union select 21
	union select 22
	union select 23) a 
 
left join count_transactions b on a.hr = Hour(b.tran_date)
WHERE DATE(b.tran_date) BETWEEN '$dateb' AND '$dateb' AND unit_serial=$netw
GROUP BY part_of_day" ) or die("SELECT Error: ".mysql_error());

Open in new window

0
 
asaidiAuthor Commented:
Hi
i did that
GROUP BY case when a.hr between 0 and 6 then 'morning'
                         when a.hr between 6 and 19 then 'daily'
                         when a.hr between 19 and 23 then 'night'
                         end" ) or die("SELECT Error: ".mysql_error());
still show me
0:00
0
All Courses

From novice to tech pro — start learning today.