Hi,

I'm trying to get totals of the number of times a client has accessed an area and grouping by the number of times they have access that area.

The query below returns everything I need except the actual totals. I've tried declaring vars and then using sum() to total the amount, but the totals get carried across all the groups.

Is there a more elegant(and correct) way to do this or must I rather use IF ELSE statements to get the correct results?

Thanks,

Query
------------
SELECT
s.SourceID as Zone,s.SourceDesc as Club,
CASE when count(distinct a.ClientID) <= 1 then 1 else 0 end as '1',
CASE when count(distinct a.ClientID) between 1 and 3 then 1 else 0 end as '2',
CASE when count(distinct a.ClientID) between 2 and 4 then 1 else 0 end as '3',
CASE when count(distinct a.ClientID) between 3 and 5 then 1 else 0 end as '4',
CASE when count(distinct a.ClientID) > 4 then 1 else 0 end as '>4',
count(s.SourceID) as Total
from tblAssessment a, tblSource s
where a.SourceID LIKE 'Kiosk%' and s.SourceID=a.SourceID
GROUP BY s.SourceDesc , s.SourceID
---------------------

Returns the following:

-------------------

Zone Club 1 2 3 4 >4 Total
---------- ------ -- -- -- -- ---- ------
Kiosk0001 duplo 0 1 1 1 0 17
Kiosk0007 sissa 0 1 1 0 0 10
Kiosk0025 freda 1 1 0 0 0 5

--------------------