Solved

morning daily and night calculation

Posted on 2012-03-15
4
241 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
insert row field data graphically 4 40
Generate PDF from MySQL using PHP 3 71
database connection error mysql stops 7 82
Building a glossary into your website 9 60
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

751 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