jeger003 Posted November 28, 2012 Share Posted November 28, 2012 hello all, I am using php and mysql to create a query. I want to create a query (without using php if possible) to count certain items and then place zero if there are none. for example i want to count hours (10am, 11am, 12pm) and number of results per hour for item1 item2. item1 - 10am - 100 results item1 - 11pm - 50 results item1 - no results for 12pm - i want mysql to put 0 instead item2 - 10am - 100 results item2 - there are none for 11am - mysql doesnt know that but I want mysql to display 0 (zero) item2 - 12pm - 200 results my query: SELECT DATE_FORMAT(`FDate`,'%H') AS hours,COUNT(Object) AS Ocount, Name, First, FROM temptable WHERE Actioned IS NULL GROUP BY Name,hours ORDER BY hours,Name DESC; but within my query i want results of 0 as well. Quote Link to comment https://forums.phpfreaks.com/topic/271302-select-if-exist-then-place-zero-in-place-if-not-exist/ Share on other sites More sharing options...
Barand Posted November 28, 2012 Share Posted November 28, 2012 To do it purely with SQL you would need a table with a row for each hour and then a left join to your table Quote Link to comment https://forums.phpfreaks.com/topic/271302-select-if-exist-then-place-zero-in-place-if-not-exist/#findComment-1395959 Share on other sites More sharing options...
jeger003 Posted November 28, 2012 Author Share Posted November 28, 2012 thanks! that was a good start for me. but here is where I am stuck SELECT DATE_FORMAT(`FDate`,'%H') AS hours, COUNT(Object) AS KeyCount, Name FROM temp LEFT JOIN ghours ON hour = DATE_FORMAT(FDate,'%H') WHERE DATE_FORMAT(`FDate`,'%m-%d') = '11-27' AND Actioned IS NULL GROUP BY Name, hours ORDER BY Name, hours DESC it doesnt return the hours of name that dont have results. it only returns those with results Quote Link to comment https://forums.phpfreaks.com/topic/271302-select-if-exist-then-place-zero-in-place-if-not-exist/#findComment-1396036 Share on other sites More sharing options...
Barand Posted November 28, 2012 Share Posted November 28, 2012 Which table contains the hours? Which is your data table. Which tables do the fields mentioned belong to? Quote Link to comment https://forums.phpfreaks.com/topic/271302-select-if-exist-then-place-zero-in-place-if-not-exist/#findComment-1396053 Share on other sites More sharing options...
jeger003 Posted November 29, 2012 Author Share Posted November 29, 2012 (edited) ghours has a list of time in 24 hour format. temp table has the data. ghours fields ID hours24 temp table FDate - 24 hour mysql time Object Name Edited November 29, 2012 by jeger003 Quote Link to comment https://forums.phpfreaks.com/topic/271302-select-if-exist-then-place-zero-in-place-if-not-exist/#findComment-1396151 Share on other sites More sharing options...
Barand Posted November 29, 2012 Share Posted November 29, 2012 use ghours LEFT JOIN temp It needs to be alldatavalues LEFT JOIN somedatavalues Quote Link to comment https://forums.phpfreaks.com/topic/271302-select-if-exist-then-place-zero-in-place-if-not-exist/#findComment-1396164 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.