Using 2 DISTINCTS in a query?

Nate

Hi, thanks for reading.

My Table: test_raw_countries

site_id name logged_date_time

1 Canada 2004-01-03 12:15:08
1 Canada 2004-01-03 11:10:29
1 Canada 2004-01-04 01:12:19
1 USA 2004-01-04 02:18:22

1 USA 2004-01-04 01:12:19

I need to select all rows where name is distinct and logged_date_time is distinct according to the date and count the results.

So for example the query needs to return the following results:

site_id name logged_date results

1 Canada 2004-01-03 2
1 Canada 2004-01-04 1
1 USA 2004-01-04 2

Notice: "Canada" is listed twice because there are 2 different distinct dates.

I tried:

SELECT DISTINCT name, DATE_FORMAT( logged_date_time, "%Y-%m-%d" ) , COUNT( * ) , site_id
FROM `test_raw_countries` 
GROUP BY logged_date_time

but this produces:

site_id name logged_date results

1 Canada 2004-01-03 1
1 Canada 2004-01-04 1
1 USA 2004-01-04 2

this is almost right but the 1st canada with the 2004-01-03 date should say results 2 not 1

help!

Lars_Berg

SELECT name, 
DATE_FORMAT( logged_date_time, "%Y-%m-%d" ) , COUNT( * ) , site_id
FROM `test_raw_countries` 
GROUP BY 
name, 
DATE_FORMAT( logged_date_time, "%Y-%m-%d" ) , site_id

All non-aggregated and non-constant expressions in the select list should be present in the group by clause.

Nate

Thank you so so much! This works perfectly.