I have two tables applications and leads
application table
submitDate | fName | lName
2010-11-15 joe smith
2010-11-16 joe smith
2010-11-15 joe smith
Leads table
submitDate | click | state
2010-11-15 1 ca
2010-11-16 1 ca
2010-11-16 1 ca
2010-11-15 1 ca
2010-11-15 1 ca
2010-11-15 1 ca
2010-11-15 1 ca
2010-11-15 1 ca
I want a query to return this result
submitDate | application | clicks | percent
2010-11-15 2 6 33%
2010-11-16 1 2 50%
I tried the following
SELECT `submitDate` , count(`submitDate`) AS 'comp',(select sum(`click`) from `leads`
WHERE `submitDate` between '2010-11-15' AND '2010-11-16' group by `submitDate`)as
'clicks' from `applications` WHERE `submitDate` between '2010-11-15' AND
'2010-11-16' group by `submitDate`
this return an error
#1242 - Subquery returns more than 1 row
and tried the following
SELECT `leads`.`submitDate` , count(`leads`.`submitDate`)
AS 'application',sum(`click`) as 'clicks'
from `applications`,`leads` WHERE `leads`.`submitDate` between
'2010-11-15' AND '2010-11-16' group by `leads`.`submitDate`
this return the following result
submitDate | application | clicks
2010-11-15 60 60
2010-11-16 6 6
leads table have 22 columns / 20 = 2010-11-15 and 2 2010-11-16 the application table as same as the above table
I'm sorry for the long explination but to be clear
any idea?
and about add new field percent that devided applications/leads SO the result will have 4 columns
thanks in advance
select d.date, coalesce(a.count, 0) as applications, coalesce(b.count, 0) as clicks
from
(
select date from applications
union
select date from leads
) d
left outer join (
select date, count(*) as count
from applications
group by date
) a on d.date = a.date
left outer join (
select date, count(*) as count
from leads
group by date
) b on d.date = b.date