October 15, 2009 at 5:52 am
Hi all,
I have a case which i can not resolve yet. Every advice will be welcome, thanks 🙂
I have one sample table
CREATE TABLE [dbo].[tbl3](
[a] [nchar](10) NULL,
[nchar](10) NULL,
[c] [int] NULL,
[d] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_tbl3] PRIMARY KEY CLUSTERED
(
[d] ASC
) ON [PRIMARY]
) ON [PRIMARY]
And data like this:
INSERT INTO tbl3(a,b,c) VALUES(1,'n',5)
INSERT INTO tbl3(a,b,c) VALUES(1,'n',10)
INSERT INTO tbl3(a,b,c) VALUES(1,'n',100)
INSERT INTO tbl3(a,b,c) VALUES(1,'c',15)
INSERT INTO tbl3(a,b,c) VALUES(2,'c',20)
INSERT INTO tbl3(a,b,c) VALUES(2,'c',25)
INSERT INTO tbl3(a,b,c) VALUES(3,'n',30)
INSERT INTO tbl3(a,b,c) VALUES(3,'n',35)
So, i want to sum column [c] and group by column [a] but exclude 'c' value of column if all value of column in group = 'c' , but if i code like this:
SELECT [a] , min() b, sum([c]) c
FROM tbl3
GROUP BY a
HAVING (MIN(b) = 'c' AND max(b) = 'n') OR (MIN(b) = 'n')
It will be sum all value in gourp include those value which equal 'c' in column.
I came up with this query,
SELECT min(Tmp.a) a,
min(Tmp.c) c,
CASE WHEN min(tbl3.b) = 'c' THEN 'c'
ELSE min(tbl3.b)
END b
FROM ( SELECT A,
case when min(b) = 'c' THEN 'c'
else min(b)
END b,
case when min(b) <> 'c' then SUM(c)
END c
FROM tbl3
WHERE b <> 'c'
GROUP BY a
HAVING ( MIN(b) = 'c'
AND max(b) = 'n'
)
OR ( MIN(b) = 'n' )
OR MIN(b) <> 'c'
) Tmp
INNER JOIN tbl3 ON Tmp.a = tbl3.a
GROUP BY Tmp.a,
Tmp.c
but this solution is not good enough, i think.
So , if you have any advice, please do not hesitate to reply.
Thanks and regards
sol
October 15, 2009 at 6:13 am
If i understand you correct
SELECT [a] , min() b,MAX() maxb, sum([c]) c
FROM tbl3
GROUP BY a
HAVING MIN()<>'c' or MAX()<>'c'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply