Query Help - Aggregate Function

[bgordon]

I have a table that collects page view stats.  One of the fields (called source) has a text value indicating which site the stat is for.  I am trying to get a query to work that returns two count values, one for site a and another for site b, by day. 

 

The plan is to pass the two counts and the group by date to a graph and show two dots for each day.  I can get this to work for one site or the other, but not both.

 

Here is my query, which I know is wrong but should help you understand what I trying to achieve:

 

SELECT

date(`date`) AS `Date`,

city,

province,

country,

count(source) as SiteA,

count(source) as SiteB

FROM pageviewstats

GROUP BY date(`date`),source

 

A couple sample table rows look like:

 

1, 2009-07-26, new york, new york, usa, website1

2, 2009-07-26, los angeles, california, usa, website2

etc...

 

I need to have a return like:

 

2009-07-25,whatever,whatever,whatever,total for website1, total for website2

2009-07-26,whatever,whatever,whatever,total for website1, total for website2

 

This lets me use the date as a label and the two totals as data sources for plotting on the chart?

 

Anyone steer me in the right direction?

[bgordon]

I have been messing around with this all day and think I might need to do some kind of self join but still cannot get the results onto a single line when grouping by date...

 

I know I could simply make a new column to track site b and then simply count(siteb) but there must be a way to do this using a single column with more than one value possibility.

 

I tried the following and really thought it might work.. but it actually returns no results...

 

SELECT

date(t1.date) AS Date,

t1.city,

t1.province,

t1.country,

count(t1.source) AS SiteA,

count(t2.source) AS SiteB

FROM pageviewstats t1 join pageviewstats t2 on t2.id = t1.id

where t1.source = 'sitea' and t2.source = 'siteb'

GROUP BY date(t1.date)

[fenway]

You need group_concat().