how do I structure this query?

[sorkIndustries]

My table has the following columns: A boolean,B integer,C varchar(40)

I want to get a count of the true and false values in A for each combination of B and C, and the percentage of true values. For example, if the table has the following values:

A B C
true 35 bob
false 35 bob
true 28 ted
true 28 ted
false 28 bob
false 35 ted

should return:

B C True False Percent
35 Bob 1 1 50%
35 Ted 0 1 0%
28 Ted 2 0 100%
28 Bob 0 1 0%

so far I have the following query:

SELECT c,b,count(a) from myTable where a='True' group by b,c

I'm not sure where to go from here. I don't know that much SQL and I'm a little lost.

 

[gmmastros]

When you use aggregates (like count), NULLS are ignored in the count. In your example, you have Count(a). So, if the value stored in the a column is true or false, it will get counted. If the value in the a column is NULL, it won't be counted. Count(*) will count all the rows whether there are null values or not.

The way you structured your query is not going to work. But, we can use the NULL property to trick the query in to giving us the correct output. By using a Case/When construct inside the count, we can return NULL or a value. Like this:

[tt][blue]
Select A, Case When A = 1 Then 1 Else NULL End As TrueOnly
From YourTable
[/blue][/tt]

When you run this query, you should notice that for every True in the A column, the 2nd column returns 1. For the False values, the 2nd column will return NULL. If we were to COUNT the second column, we would effectively get a count of the true values. We can do something similar for the false values, where we return a 1 for false and a NULL for true. Expanding on this...

Select B,
       C,
       Count(Case When A = 1 Then 1 End) As CountTrue,
       Count(Case When A = 0 Then 1 End) As CountFalse,
       100.0 * Count(Case When A = 1 Then 1 End) / Count(*) As PercentTrue
From   YourTableName
Group By B, C

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sorkIndustries]

Thanks, that worked well!