Reputation: 60912
Here's my query
SELECT
client_id,
TimesTested,
CAST(COUNT(*) AS varchar(30)) AS count,
CAST(AVG(testfreq) as varchar(30)) as TestFreq,
CAST(STDEV(TestFreq) as varchar(30)) Stdv
FROM counted
GROUP BY
client_id,
TimesTested
It works; however, i need filter the AVG and STDEV where testfreq>0 but I need the count(*) to be not filtered.
The closest thing would be:
SELECT
client_id,
TimesTested,
CAST(COUNT(*) AS varchar(30)) AS count,
CAST(AVG(testfreq) as varchar(30)) as TestFreq,
CAST(STDEV(TestFreq) as varchar(30)) Stdv
FROM counted
where testfreq>0 --however I don't want this filtered applied to count(*)
GROUP BY
client_id,
TimesTested
Thanks so much for your guidance!
Upvotes: 0
Views: 44
Reputation: 247880
You should be able to use a CASE
statement in your aggregate similar to this:
SELECT client_id,
TimesTested,
CAST(COUNT(*) AS varchar(30)) AS count,
CAST(AVG(case when testfreq>0 then testfreq end) as varchar(30)) as TestFreq,
CAST(STDEV(case when testfreq>0 then TestFreq end) as varchar(30)) Stdv
FROM counted
GROUP BY client_id, TimesTested
I am not sure why you need these as varchars, is there a reason you need these values converted to varchar
?
I would just leave the values as is:
SELECT client_id,
TimesTested,
COUNT(*) AS count,
AVG(case when testfreq>0 then testfreq end) as TestFreq,
STDEV(case when testfreq>0 then TestFreq end) Stdv
FROM counted
GROUP BY client_id, TimesTested
Upvotes: 2