June 19, 2009 at 9:44 am
I am trying to get a count() of MIn() valued rows and count() of max() value rows for distinct groups.
I have tried this:
select ctl as Type, min(si) as Minimum, count(min(si)), max(si) as Maximum, avg(si) as Average from timberstands
group by ctl
order by ctl
I get an error when I try this.
If I just use cout(*) all I get is rows by group.
Any advice would be greatly appreciated.
Thanks
Quinn
June 19, 2009 at 11:34 am
I don't really understand what you're looking for...
Assuming you don't have duplicate values in col [si], you should get "1" for count(min) and "1" for count(max), once the syntax is fixed... Is that really what you're looking for?
In order to help us help you please provide sample data as described in the link in my signature.
June 19, 2009 at 11:47 am
I do not believe you can do a count on an aggregate function, i.e, COUNT(MIN(si)). I believe the data would have to be broken up into seperate results then a final query run on the data... without seeing some of the data or get an idea as to the final result, it is hard to guess at what is being requested and a possible solution.
From BOL on COUNT: ...Aggregate functions and subqueries are not permitted.
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
June 19, 2009 at 11:57 am
the data would have to be broken up into separate results then a final query run on the data
is a little more detailed explanation what I was thinking of when stating "once the syntax is fixed".
If the number of min() values really needs to get counted I'd use CTEs to do it. This way it still would be one query...
June 19, 2009 at 12:10 pm
Thanks guys. Sorry about the confusion.
I need to show how many rows there are of the min and max values in each grouping. Lets say that there are 100 rows in one group. How many of those rows are the min() value say 25 and how many of the rows are the max value say another 25 rows. Then we can assume that 50 rows are other values.
I will work on getting a set of data to look at.
Quinn
June 19, 2009 at 12:23 pm
Here is a chunk of data.
CTL SI
LG0
BSL41
SX19
BSL31
A61
A61
BSL41
LG0
BSL41
A61
A68
BSL36
A58
BSL38
LG0
LB0
A72
A72
LB0
SX19
LF0
LB0
LB0
T49
BSL26
T61
SX19
BSL24
T65
BSL27
BSL27
Ash38
BSL31
UB0
BSL26
C32
LB0
BSL39
C55
A70
A70
BSL30
C31
T49
T49
C24
T50
BSL31
LB0
A78
BSL18
C32
LB0
C29
SX18
AX33
A80
A68
LB0
BSL25
June 19, 2009 at 1:05 pm
DECLARE @t TABLE (CTL VARCHAR(10), SI INT)
INSERT INTO @t
SELECT ' LG',0 UNION ALL
SELECT ' BSL',41 UNION ALL
SELECT ' SX',19 UNION ALL
SELECT ' BSL',31 UNION ALL
SELECT ' A',61 UNION ALL
SELECT ' A',61 UNION ALL
SELECT ' BSL',41 UNION ALL
SELECT ' LG',0 UNION ALL
SELECT ' BSL',41 UNION ALL
SELECT ' A',61 UNION ALL
SELECT ' A',68 UNION ALL
SELECT ' BSL',36 UNION ALL
SELECT ' A',58 UNION ALL
SELECT ' BSL',38 UNION ALL
SELECT ' LG',0 UNION ALL
SELECT ' LB',0 UNION ALL
SELECT ' A',72 UNION ALL
SELECT ' A',72 UNION ALL
SELECT ' LB',0 UNION ALL
SELECT ' SX',19 UNION ALL
SELECT ' LF',0 UNION ALL
SELECT ' LB',0 UNION ALL
SELECT ' LB',0 UNION ALL
SELECT ' T',49 UNION ALL
SELECT ' BSL',26 UNION ALL
SELECT ' T',61 UNION ALL
SELECT ' SX',19 UNION ALL
SELECT ' BSL',24 UNION ALL
SELECT ' T',65 UNION ALL
SELECT ' BSL',27 UNION ALL
SELECT ' BSL',27 UNION ALL
SELECT ' Ash',38 UNION ALL
SELECT ' BSL',31 UNION ALL
SELECT ' UB',0 UNION ALL
SELECT ' BSL',26 UNION ALL
SELECT ' C',32 UNION ALL
SELECT ' LB',0 UNION ALL
SELECT ' BSL',39 UNION ALL
SELECT ' C',55 UNION ALL
SELECT ' A',70 UNION ALL
SELECT ' A',70 UNION ALL
SELECT ' BSL',30 UNION ALL
SELECT ' C',31 UNION ALL
SELECT ' T',49 UNION ALL
SELECT ' T',49 UNION ALL
SELECT ' C',24 UNION ALL
SELECT ' T',50 UNION ALL
SELECT ' BSL',31 UNION ALL
SELECT ' LB',0 UNION ALL
SELECT ' A',78 UNION ALL
SELECT ' BSL',18 UNION ALL
SELECT ' C',32 UNION ALL
SELECT ' LB',0 UNION ALL
SELECT ' C',29 UNION ALL
SELECT ' SX',18 UNION ALL
SELECT ' AX',33 UNION ALL
SELECT ' A',80 UNION ALL
SELECT ' A',68 UNION ALL
SELECT ' LB',0 UNION ALL
SELECT ' BSL',25
;with cte
AS
(
SELECT
ctl AS ctl,
MIN(si) AS mi_si,
MAX(si) AS ma_si,
AVG(si) AS avg_si
FROM @t
GROUP BY ctl
)
, cte_mi_si
AS
(
SELECT
cte.ctl AS ctl,
COUNT(*) AS cnt_mi_si
FROM cte INNER JOIN @t t ON cte.ctl = t.ctl AND cte.mi_si = t.si
GROUP BY cte.ctl
)
,cte_ma_si
AS
(
SELECT cte.ctl AS ctl,
COUNT(*) AS cnt_ma_si
FROM cte INNER JOIN @t t ON cte.ctl = t.ctl and cte.ma_si = t.si
GROUP BY cte.ctl
)
SELECT
cte.*,
cnt_mi_si,
cnt_ma_si
FROM cte INNER JOIN cte_mi_si ON cte_mi_si.ctl = cte.ctl
INNER JOIN cte_ma_si ON cte_ma_si.ctl = cte.ctl
ORDER BY cte.ctl,cte.mi_si
June 19, 2009 at 1:23 pm
Lutz
THat did it. Thanks. Sorry I didn't have my data pposted correctly. It skipped my mind.
I learned a bit about subqueries on that.
Thanks again.
Quinn
June 19, 2009 at 1:39 pm
June 19, 2009 at 2:04 pm
after playing around a little bit I found another way to do it which might be faster on larger amount of data, since it's using less subqueries.
The "trick" is to replace the count(*) function by a SUM(CASE 1 ELSE 0) function.
;with cte
AS
(
SELECT
ctl AS ctl,
MIN(si) AS mi_si,
MAX(si) AS ma_si,
AVG(si) AS avg_si
FROM @t
GROUP BY ctl
)
SELECT
cte.ctl,
MIN(t.si) AS mi_si,
MAX(t.si) AS ma_si,
AVG(t.si) AS avg_si ,
SUM(CASE si WHEN cte.mi_si THEN 1 ELSE 0 END) AS cnt_mi_si,
SUM(CASE si WHEN cte.ma_si THEN 1 ELSE 0 END) AS cnt_ma_si
from @t t INNER JOIN cte on t.ctl=cte.ctl
group by cte.ctl
ORDER BY cte.ctl
June 19, 2009 at 2:15 pm
Also work and also cool.
Like you said quite a bit shorter.
Thanks
Quinn
June 19, 2009 at 2:52 pm
You're welcome, again. 🙂
It's always good to get some feedback how the proposed solution works out for the OP. Thank you! 😉
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply