• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

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
0
asaidi
Asked:
asaidi
  • 2
  • 2
1 Solution
 
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
 
johanntagleCommented:
it has to be group by case ..... end not group by part_of_day
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now