Grouping total results problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Grouping total results problem

Author

Topic

repsycle
Starting Member

5 Posts

Posted - 2004-05-20 : 06:12:24

Hi,

I'm trying to get totals of the number of times (results) 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

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

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-05-20 : 08:24:03

I don't understand. Dont you already have a total? Could you give us an example of the resultset you expect?

OS

repsycle
Starting Member

5 Posts

Posted - 2004-05-20 : 08:29:07

The resultset I'm looking for is this:

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

Basically, the number of times a result occurs (1,2,3 ect times) must add up to the total (5+6+6 = 17)

I've tried tackling this query from a few angles (except using cursors) with no joy

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-05-20 : 08:39:49

I'm not quite sure what the implications of having the DISTINCT in there are, but does this work?


SELECT
s.SourceID as Zone,s.SourceDesc as Club,
SUM(CASE when count(distinct a.ClientID) <= 1 then  else 0 end) as '1',
SUM(CASE when count(distinct a.ClientID) between 1 and 3 then 1 else 0 end) as '2',
SUM(CASE when count(distinct a.ClientID) between 2 and 4 then 1 else 0 end) as '3',
SUM(CASE when count(distinct a.ClientID) between 3 and 5 then 1 else 0 end) as '4',
SUM(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

repsycle
Starting Member

5 Posts

Posted - 2004-05-20 : 08:44:31

thanx for the quick reply OS.

I've tried using SUM() around the CASE but get a 'Cannot perform an aggregate function on an expression containing an aggregate or a subquery.' error

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-05-20 : 09:13:14

See if this'll work:

SELECT	  Zone
	, Club
	, SUM(CASE when ClientCount <= 1 then 1 else 0 end) as '1',
	, SUM(CASE when ClientCount between 1 and 3 then 1 else 0 end) as '2',
	, SUM(CASE when ClientCount between 2 and 4 then 1 else 0 end) as '3',
	, SUM(CASE when ClientCount between 3 and 5 then 1 else 0 end) as '4',
	, SUM(CASE when ClientCount > 4 then 1 else 0 end) as '>4',
	, Total

FROM	(
	SELECT	  s.SourceID as Zone
		, s.SourceDesc as Club
		, count(distinct a.ClientID) as ClientCount
		, 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
	) x
GROUP BY SourceDesc, SourceID, Total

BTW you have significant overlapping of your "ranges" a Client Count of 3 within a group will be counted in three places the 1-3 range, the 2-4 range, and the 3-5 range. FYI, BETWEEN is inclusive.

repsycle
Starting Member

5 Posts

Posted - 2004-05-20 : 09:38:46

drymchaser,

Ta for the response

Funnily enough, I get exactly the same resultset using ur query, but at least there is no error

The totals still don't match :(

Ta for the BETWEEN pointer!

+++++++++++++++++
modified query:

SELECT Zone, Club,
SUM(CASE when ClientCount <= 1 then 1 else 0 end) as '1',
SUM(CASE when ClientCount = 2 then 1 else 0 end) as '2',
SUM(CASE when ClientCount = 3 then 1 else 0 end) as '3',
SUM(CASE when ClientCount = 4 then 1 else 0 end) as '4',
SUM(CASE when ClientCount > 4 then 1 else 0 end) as '>4',
Total
FROM (
SELECT s.SourceID as Zone
, s.SourceDesc as Club
, count(distinct a.ClientID) as ClientCount
, 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
) as x
GROUP BY Zone, Club, Total

repsycle
Starting Member

5 Posts

Posted - 2004-05-20 : 11:13:24

Thanks for all the help!!

Final working query:

--------------------------
SELECT Zone, Club,
SUM(CASE when ClientCount <=1 then 1 else 0 end) as '1',
SUM(CASE when ClientCount =2 then 1 else 0 end) as '2',
SUM(CASE when ClientCount =3 then 1 else 0 end) as '3',
SUM(CASE when ClientCount =4 then 1 else 0 end) as '4',
SUM(CASE when ClientCount >4 then 1 else 0 end) as '>4'
FROM (
SELECT s.SourceID as Zone,
s.SourceDesc as Club,
count(a.ClientID) as ClientCount
from tblAssessment a, tblSource s
where a.SourceID LIKE 'Kiosk%' and s.SourceID=a.SourceID
GROUP BY s.SourceID, s.SourceDesc , a.ClientID
) as x
GROUP BY Zone, Club ORDER BY Zone
------------------------------------

Peace and Respect

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources