how do i build this query?

Question

Post reply

how do i build this query?

Blair Dee

SSCommitted

Points: 1648
More actions
October 13, 2005 at 7:49 am

#91765

i have a table with the following fields ID, GroupCode, Compliant.
I need to build a query that will output 3 columns grouped by GroupCode:
Column 1 will be the groupcode
Column 2 will be the number of times that a record with this group code exists in the database table
Column 3 will be the number of times that a record with this groupcode and where the compliant field is 1
so basically the resulting table will tell us that in the table there were 14 records with groupCode 1, and of those 14, 6 were compliant.
I figured out how to get the data with 2 separate queries which are as follows:
SELECT GroupCode, COUNT(*) as myCount
FROM myTbl
GROUP BY GroupCode
this gives us the total number of each GroupCode Occurrences
SELECT GroupCode, COUNT(*) as myCount
FROM myTbl
WHERE compliant = 'T'
GROUP BY GroupCode
this gives us the total number of compliant records for each group code.
I just cant seem to figure out how to combine the 2. sorry if this seems basic to some of you - i am still learning. and would appreciate any help.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

Haven't tested this, but:

SELECT GroupCode, COUNT(*) AS MyCount, T.MyTCount

FROM myTbl

INNER JOIN( SELECT GroupCode, COUNT(*) AS MyTCount

FROM myTbl

WHERE compliant = 'T'

GROUP BY GroupCode) T ON( myTbl.GroupCode = T.GroupCode)

GROUP BY GroupCode

I wasn't born stupid - I had to study.

Blair Dee SSCommitted Points: 1648 More actions · Answer 2

hey thanks for the quick reply - i tried that and i keep getting the error : Column 'T.MyTCount' is invalid oin the select list because it is not contained in either an aggregate function or the GROUP BY clause.

but perhaps more importantly i woudl like to understand what it is that you are doign inthat query. where do you get T.myTCount from? coudl you please help me understand? as i said i am still learning.

thanks!!

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

Like I said, I threw this together without testing it. This should solve that problem:

SELECT GroupCode, COUNT(*) AS MyCount, T.MyTCount

FROM myTbl

INNER JOIN( SELECT GroupCode, COUNT(*) AS MyTCount

FROM myTbl

WHERE compliant = 'T'

GROUP BY GroupCode, compliant ) T ON( myTbl.GroupCode = T.GroupCode)

GROUP BY GroupCode

Basically what you are doing is create a 'derived' table and calling it 'T'. You are joining it to the original table on the common field, (hopefully Primary Key for your query).

Hope that is enough explanation. (I learned that method from this site by someone else posting an answer for me - so I am happy to share it with someone else )

I wasn't born stupid - I had to study.

Blair Dee SSCommitted Points: 1648 More actions · Answer 4

ok i think i can see what you are trying to do here but unfortunately i am still getting the same error when i run the query.

i tried adding the T.myTCount field to the main GROUP By clause and it seems to work. so my query looks like this:

SELECT mytbl.GroupCode, COUNT(*) AS myCount, T.MyTCount

FROM myTbl INNER JOIN

(SELECT GroupCode, COUNT(*) AS MyTCount

FROM myTbl

WHERE compliant = 'T'

GROUP BY GroupCode) T ON myTbl.RIC = T.RIC

GROUP BY myTbl.RIC, T.MyTCount

thanks for sharing!!!

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

You got it! I missed that one as well...

I wasn't born stupid - I had to study.

David Burrows SSC Guru Points: 65143 More actions · Answer 6

Two observations

Your last posted query uses a column named [RIC] which is not specified elsewhere and therefore the query will not work

The query uses an INNER JOIN which means you will only get GroupCodes where a row exists with [complient] = 'T' (maybe this is what you want )

Try this

SELECT myTbl.GroupCode, COUNT(*) AS myCount, ISNULL(T.MyTCount,0) AS [TCount]

FROM myTbl

LEFT OUTER JOIN (SELECT x.GroupCode, COUNT(*) AS [MyTCount]

FROM myTbl x

WHERE x.compliant = 'T'

GROUP BY x.GroupCode) T ON myTbl.GroupCode = T.GroupCode

GROUP BY myTbl.GroupCode, T.MyTCount

or depending on the table definition and the performance is not too bad try this

SELECT GroupCode,

COUNT(*) AS myCount,

SUM(CASE WHEN compliant = 'T' THEN 1 ELSE 0 END) AS [TCount]

FROM myTbl

GROUP BY GroupCode

check the execution plans to see which performs best

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

Mike Kendall SSC Eights! Points: 958 More actions · Answer 7

SELECT GroupCode,COUNT(*) as myCount,

SUM(CASE WHEN Compliant = 'T' THEN 1 ELSE 0 END ) AS Compliant

FROM myTbl

GROUP BY GroupCode