Solved

morning daily and night calculation

Posted on 2012-03-15
4
232 Views
Last Modified: 2012-03-18
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
Comment
Question by:asaidi
  • 2
  • 2
4 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 37727621
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
 

Author Comment

by:asaidi
ID: 37728944
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
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 37729069
it has to be group by case ..... end not group by part_of_day
0
 

Author Comment

by:asaidi
ID: 37729788
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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now