Here's my query:
select COUNT(*) as total, COUNT(distinct user) as unique
FROM table
WHERE uristem in ('/example/', '/example/', '/example/', '/example/')
and time > DATE_SUB(NOW(), INTERVAL 24 HOUR)'
group by uristem;
Should look like this
100 50
25 50
0 0
100 35
But if there is no count, it looks like this (missing)
100 50
40 50
100 35
How can I fill in 0's instead?
Try this (untested):
select COUNT(t.uristem) as total
, COUNT(distinct t.user) as unique
FROM
(
select uristem, user
from table
where time > DATE_SUB(NOW(), INTERVAL 24 HOUR)
) t
right outer join
(
select '/example/' x
union select '/example/'
union select '/example/'
union select '/example/'
) y
on t.uristem = y.x
group by y.x;
If you don't have a table with the uri stems in then something ugly like
Select
u.uristem,
count(t.uristem),
count(distinct t.user) as unique
From (
Select '/example1/' As uristem
Union All Select '/example2/'
Union All Select '/example3/'
Union All Select '/example4/'
) u
Left Outer Join
table t
On u.uristem = t.uristem And
t.time > Date_Sub(Now(), Interval 24 Hour)
Group By
u.uristem
Should do it
