sql query  help needed!!

Posted on 2003-11-12
Last Modified: 2012-08-13
Hello All,

In the following query i m selecting a few columns and making a sum of hours...
but b'coz of the groupby clause for datetime_from..i m not able to get the
absent days in the hours_booked table.(where sum should be 0)...i want the days
which are not present in the table with the sum value as 0.

How i can do this?? the database i m using is MySql.

SELECT  DATE_FORMAT(datetime_from,'%M-%Y') AS monthOfYear ,  
DATE_FORMAT(datetime_from,'%W') AS dayOfMonth ,  
DATE_FORMAT(datetime_from,'%Y-%m-%d') AS date ,  
(SUM(UNIX_TIMESTAMP(datetime_to)-UNIX_TIMESTAMP(datetime_from))/3600) AS hours  
FROM hours_booked
WHERE UPPER(owner) = UPPER('something')
AND DATE_FORMAT(datetime_from,'%M-%Y')='May-2003'
GROUP BY DATE_FORMAT(datetime_from,'%Y-%M-%d')  
ORDER BY DATE_FORMAT(datetime_from,'%Y-%m-%d') DESC

Please guide.
Thanks and Regards
Question by:aks143
  • 3

Expert Comment

ID: 9737235
There's no real easy way to do this in mysql as far as I know, but wait a couple days and maybe someone will come up with more elegant answer than these, but I'll take a stab:

1) This might be easier in the language you're connecting to the DB with (like VB or PHP).  Checking for out of sequence dates, and inserting the zeros as neccessary.  It's at least worth considering.  In general I'm 100% behind moving as much computation into the database, as they're extremely powerful tools, but I just don't think that mysql can handle this as gracefully as one would want.

2) To do it in the DB, I think the easiest way might be to create a table called something like tbl_aux_dates.  This table can have just one field called, for example, auxdate.  You can populate the table with one of each of the possible dates (so it would have one entry for every possible day), and then do an outer join between hours_booked and tbl_aux_dates and make everything in your where clause and group by refer to the auxdate field.  So, the query would look approximately like this:

SELECT  DATE_FORMAT(A.auxdate,'%M-%Y') AS monthOfYear ,  
DATE_FORMAT(A.auxdate,'%W') AS dayOfMonth ,  
DATE_FORMAT(A.auxdate,'%Y-%m-%d') AS date ,  
IFNULL(((SUM(UNIX_TIMESTAMP(datetime_to)-UNIX_TIMESTAMP(datetime_from))/3600)), 0) AS hours  
FROM tbl_aux_dates as A LEFT OUTER JOIN hours_booked as B
ON (A.auxdate=B.datetime_from)
WHERE UPPER(B.owner) = UPPER('something')
AND DATE_FORMAT(A.auxdate,'%M-%Y')='May-2003'
GROUP BY DATE_FORMAT(A.auxdate,'%Y-%M-%d')  
ORDER BY DATE_FORMAT(A.auxdate,'%Y-%m-%d') DESC

We have to use left outer join to make sure that even rows in tbl_aux_date that don't have corresponding rows in hours_booked are displayed.  More info on left outer join can be found here:

And the IFNULL() is used in case the datetime_to is NULL, as should be if there isn't a corresponding row in hours_booked.  This is the one thing I'm not 100% sure on.  I'm 99% sure that it will work, but there's a lot of null values floating around there.  If it doesn't work, try out some different control flow functions.  You can find them here:

I hope that helps... and makes some sort of sense.  I'm not sure if I explained it very well.  And like I said, someone could have a solution that's much better than this one.  This assumes that when you say that the dates don't appear in the main table, they really don't appear anywhere in the table.  As in, if a date has no hours booked, then it has no entry whatsoever in the table.  Good luck!

Expert Comment

ID: 9737434
Sorry, the line that starts IFNULL should have the aliases in it as well, so it would actually be

IFNULL(((SUM(UNIX_TIMESTAMP(B.datetime_to)-UNIX_TIMESTAMP(B.datetime_from))/3600)), 0) AS hours  

Author Comment

ID: 9747220

Thanks for your answer. I think the second option is more appropriate. I was also thinking of the same, but the idea of having all the possible dates..somehow didn't suits me. I mean to say, the hours_booked table has data coming in every day...either my aux_date table should have all the dates for the given year...OR all the absent dates in the hours_booked table. But, what is the flexible way to create such kind of aux_date table?? How can i insert in my aux table..all the days in a year.


Accepted Solution

spike3382 earned 75 total points
ID: 9748130
Right... and that's the problem with mySQl at the moment.  There is no flexible way to do it without relying on third party software.  Off the top of my head, probably the best way to do it would be to connect to the database with PHP or VB or whatever the language is that you're using to talk to the database, and then to get the UNIX timestamp for the current date, and write a loop that adds that as a date into the mysql database, and loops backwards 356 times subtracting 86,400 seconds (the number of seconds in a day) from the timestamp and adding that as a day, and then another one that loops forwards 356 times adding 86,400 seconds every time and adding that as a possible day.  That'd give you 2 years of dates to work with.  Of course you can up the iterations of the loop in either direction to give you more days.   Never having had to do this myself, and depending on what language you're using, there could be an easier way, although I think that's about as elegant as you're going to get with MySQL in its current state.

Hope that helps,

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

Suggested Solutions

Title # Comments Views Activity
join tables 4 56
AWS EC2 & RDS Instance 5 50
Optimize the query 5 43
How to select record with In and condition together based on more then one coulmn 7 27
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…
Creating and Managing Databases with phpMyAdmin in cPanel.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below.…

828 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