Select If Exist Then Place Zero In Place If Not Exist

[jeger003]

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.

[Barand]

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

[jeger003]

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

[Barand]

Which table contains the hours?

Which is your data table.

Which tables do the fields mentioned belong to?

[jeger003]

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

[Barand]

use

 

ghours LEFT JOIN temp

 

It needs to be

alldatavalues LEFT JOIN somedatavalues