sql server count

one NEVER (never ever!) needs BOTH "select distinct" and "group by" in a single query

GROUP BY produces rows that are unique for the combination of columns you nominate in that clause

SELECT DISTINCT - which is performed AFTER the group by - then checks if all the rows are unique, but they already are, so "select distinct" is just a waste of effort when you are doing group by too.

Chaau's query is absolutely correct just note you do not need to include schedule.clientid in the group or the select clauses, like this:

select count(scheduleId), client.clientname from schedule
inner join client on client.clientid = schedule.clientid
group by client.clientname 
order by client.clientname 

Select allOpen in new window

no points please

Hi,

Hope this query will help you if you have duplicate scheduleid then yo have to use Distinct in the count

select  count(distinct scheduleId), clientname from schedule
inner join client on client.clientid = schedule.clientid
group by clientname

Select allOpen in new window

With a little tricky use of MIN you can have the advantage to group by the unique clientid and still show the clientname in the result:

select  count(distinct scheduleId) as schedulecount, schedule.clientid, 
MIN(schedule.clientname) as clientname
from schedule inner join client on client.clientid = schedule.clientid
group by schedule.clientid

Select allOpen in new window

Group By needs every single standing field to be grouped or aggregated, since all clientnames, so not grouping on clientname would cause an error, but since any clientid has just one clientname, MIN(clientname) is the same as clientname per clientid and satisfies the group by rules.

If you don't need the name you don't even need a join and it all boild down to

select  count(distinct scheduleId) as schedulecount, clientid from schedule
group by clientid

Select allOpen in new window

Bye, Olaf.

You only need the schedule table because you have a clientid value associated with any schedule:

select 
    clientid,
    count(*) as schedule_count
from
    schedule
group by 
    clientid

Select allOpen in new window

If you need the names then you can join:

select 
    clientid,
    min(clientname) as clientname,
    count(*) as schedule_count
from
    schedule s
    inner join client c
	   on c.clientid=s.clientid
group by 
    s.clientid

Select allOpen in new window

thanks all!