distinct hits

[brown2005]

SELECT DISTINCT hits_user, year( hits_date ) AS hits_year, COUNT( hits_user ) AS hits_unique, SUM( hits_count ) AS hits_count

FROM a

GROUP BY year( hits_date )

ORDER BY year( hits_date ) DESC

LIMIT 0 , 30

 

i have the above which outputs

 

hits_user          hits_year  hits_unique  hits_count 

185.584.584      2007        2                100

 

but the hits unique bit should be 1 as there are two records with  185.584.584 in..

 

can anyone help please?

[monk.e.boy]

eh? You are counting the number of rows that match '185.584.584'.

 

What did you want it to display?

 

monk.e.boy

[brown2005]

hits_user          hits_year  hits_unique  hits_count 

185.584.584      2007        2                100

 

that is what it is displaying at the moment

 

i want it to display

 

hits_user          hits_year  hits_unique  hits_count 

185.584.584      2007        1              100

 

because 185.584.584 has visited the site two different days, but is only 1 unique hit. if you know what i mean?

[monk.e.boy]

Using DISTINCT will always return you one row.

 

Using DISTINCT with ORDER BY is pointless because there is only one row, so it doesn't make sense to order it.

 

Try looking up the SQL commands in google.

 

monk.e.boy

[monk.e.boy]

hits_user          hits_year  hits_unique  hits_count 

185.584.584      2007        2                100

 

that is what it is displaying at the moment

 

i want it to display

 

hits_user          hits_year  hits_unique  hits_count 

185.584.584      2007        1              100

 

because 185.584.584 has visited the site two different days, but is only 1 unique hit. if you know what i mean?

 

I just read your reply 

 

Don't bother with hits_unique, because you have DISTINCT. There will only be one row returned. So you don't need to calculate '1' as it is always 1

 

monk.e.boy

[brown2005]

yeah but what i am doing is creating a hits list for years

 

so

 

2006 15   1

2007 24   1

 

so i need the order by, dont I?

 

there will also be lots more diffrent hits_user than 185.584.584  as i was just using that to try and get the code to work