Query Result

Question

Query Result

ko995

Newbie

Points: 6
More actions
August 8, 2006 at 10:13 am

#114506

Hello,
I have the following:
create table Test(
A int,
B int,
C int,
D int,
E int)
Go
INSERT INTO Test(A,B,C,D,E) VALUES (1,2,3,4)
INSERT INTO Test(A,B,C,D,E) VALUES (2,2,3,4)
INSERT INTO Test(A,B,C,D,E) VALUES (2,3,3,4)
INSERT INTO Test(A,B,C,D,E) VALUES (3,2,2,4)
INSERT INTO Test(A,B,C,D,E) VALUES (3,2,3,4)

Now , I want to get the result where c=3

select A, B, count(c)/count(A) as rate, count(A) as tot
from Test
group by A, B

the column tot should have a total count based on the group by of A, and B.

Thanks

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

I'm a little unclear on what you are asking for. If you just want to limit your groupings by only grouping A, B when c = 3, then just do this:

select A, B, count(c)/count(A) as rate, count(A) as tot

from @test-2

where c=3

group by A, B

Otherwise, can you show us how you want your return data to look based upon the test values you've shown?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

ko995 Newbie Points: 6 More actions · Answer 2

Thanks for the reply, to exaplain the problem use this following example and see the expected result.

Create table Test(

Col1 char(1),

Col2 int)

Go

INSERT INTO Test(col1,col2) VALUES (‘A’,1)

INSERT INTO Test(col1,col2) VALUES (‘A’,0)

INSERT INTO Test(col1,col2) VALUES (‘B’,1)

INSERT INTO Test(col1,col2) VALUES (‘B’,0)

INSERT INTO Test(col1,col2) VALUES (‘B’,1)

Now , I want to get the result where Col2=1

select col1, , count(col2)=1/count(col2) as rate, count(A) as total

from Test

group by col1,col2

The data will be displayed as following

Col1 Rate Total

A 2/3 3

B 3 /4 4

Thanks

Sergiy SSC Guru Points: 110209 More actions · Answer 3

select col1, count(CASE col2=1 THEN 1 ELSE NULL END)/count(col2) as rate, count(A) as total

from Test

group by col1,col2

_____________
Code for TallyGenerator

David Burrows SSC Guru Points: 65144 More actions · Answer 4

SELECT col1,

CAST(SUM(ABS(SIGN(Col2 - 1)) ^ 1) as varchar) + '/' + CAST(COUNT(*) as varchar) AS [Rate],

COUNT(*) AS [Total]

FROM @test-2

GROUP BY col1

Far away is close at hand in the images of elsewhere.
Anon.

ko995 Newbie Points: 6 More actions · Answer 5

ko995

Newbie

Points: 6

August 9, 2006 at 8:07 am

#654076

Thanks both are working.