January 13, 2004 at 10:46 pm
I have a table with client_ref varchar(15) and a service varchar(40) fields (amongst others). I'm trying to get MySQL to return how many services of a given type apply to the client_ref in a given time frame.
I've come up with the following code but, because a record can't have a service of type X and type Y at the same time, this code fails.
SELECT v1.client_ref as Client, count(v1.service) as 'Short Form', count(v2.service) as Progress
FROM valuations v1
INNER JOIN valuations v2 on v1.val_id = v2.val_id
WHERE v1.branch_ref='Adelaide'
AND v1.short_status='Completed'
AND v1.service='X'
AND v2.service='Y'
AND v1.completion_date >= '2003-11-01'
AND v1.completion_date <= '2003-11-30 23:59:59'
group by Client
order by 'Short Form' desc
A brief version of the table data would be as follows:
Client_ref Service
abc X
abc X
abc Y
mno X
The result set I'm after is:
Client ServiceX ServiceY
abc 2 1
mno 1 0
Hope this makes sense.
Brett
January 14, 2004 at 8:15 am
I think if you use something like the following a join is not needed.
select client,sum(servicex) as ServiceX,sum(servicey) as ServiceY from
(
select client,case service when 'x' then 1 else 0 end as servicex,case service when 'y' then 1 else 0 end as servicey
from valuations
where clause here
) a
group by client
Let us know.
Tom
January 15, 2004 at 3:58 pm
Thanks Tom,
You headed me on the right path to a solution. I ended up using a series of IF statements to increment counters as follows:
What I couldn't get it to do was use the aliases in calculated fields ie in lines 2 and 3 I created aliases but in line 4 I wasn't able to go:
count(service) - ('Short Form' + Progress) as Other
I kept getting an error saying that the column name couldn't be found (or something equivalent). I understand why it was saying that but haven't worked out a solution yet.
Thanks again
Brett
January 16, 2004 at 7:01 am
Here is your query back using a bucket approach. I would be interested to see if performance is different using the case instead of the if.
Let us know.
Tom
SELECT CLIENT
, sum([SHORT FORM])[SHORT FORM]
, sum(PROGRESS)PROGRESS
, sum(OTHER)OTHER
, sum(total)total
FROM (
SELECT CLIENT_REF AS CLIENT,
CASE SERVICE WHEN 'X' THEN 1 ELSE 0 END AS [SHORT FORM],
CASE SERVICE WHEN 'Y' THEN 1 ELSE 0 END AS PROGRESS,
CASE SERVICE WHEN 'X' THEN 0
WHEN 'Y' THEN 0
ELSE 1 END AS OTHER
CASE SERVICE WHEN 'X' THEN 1
WHEN 'Y' THEN 1
ELSE 1 END AS total
FROM VALUATIONS
WHERE BRANCH_REF = @BVAR
AND COMPLETION_DATE >= @CDVAR
AND COMPLETION_DATE <= @CDVAR
AND SHORT_STATUS='COMPLETED'
) A
GROUP BY CLIENT
ORDER BY [SHORT FORM] DESC
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply