SQL Query group by two conditions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Query group by two conditions

Author

Topic

mario_silent
Starting Member

1 Post

Posted - 2011-04-01 : 17:07:43

Hi, I have a scenario where I have 1 table, 2 columns. Gate and Valid. Gate es the gate number and Valid is either a 0 or 1. I'm working on a graphic so I can display a comparison of each gate, so I'm trying to get a query where I have Gate, count of 1's and count of 0's so I can bind it to my chart. I'm grouping by Gate and getting the count, the problem is the condition, I can't get both there and I don't know how I could structure a subquery like this:

select Gate = 'Gate' + Cast(gate as varchar(10))
, Invalid = (select count(Validation) from Products where Validation =0 and Gate = each Gate ? ), Valid= (select count(Validation) from Products where Validation =1 and Gate = each Gate ?) from Products

group by Gate

Is this possible? or do I have to look for a different approach? thanks in advance! I'm quite confused

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-01 : 17:12:11

Try it:

select Gate = 'Gate' + Cast(gate as varchar(10)),
Invalid = (select count(Validation) from Products where Validation =0 and Gate = f.Gate),
Valid= (select count(Validation) from Products where Validation =1 and Gate = f.Gate)
from Products f
group by Gate

______________________

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-01 : 17:14:37


SELECT Gate = 'Gate' + CAST(gate AS VARCHAR(10)),
       SUM(CASE WHEN validation = 0 THEN 1 ELSE 0 END) AS Invalid,
       SUM(CASE WHEN validation = 1 THEN 1 ELSE 0 END) AS Valid
  FROM Products]
 GROUP BY gate;

______________________

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-01 : 17:16:33

Also you can avoid at ELSE in the CASE statement like this:


SELECT Gate = 'Gate' + CAST(gate AS VARCHAR(10)),
       COUNT(CASE WHEN validation = 0 THEN 1 END) AS Invalid,
       COUNT(CASE WHEN validation = 1 THEN 1 END) AS Valid
  FROM Products]
 GROUP BY gate;

______________________

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-04-01 : 18:08:47

[code]
select 'Gate' + CAST(gate AS VARCHAR(10)) as Gate,
sum(1-validation) as Invalid,
sum(validation) as Valid
from products
group by gate[/code]

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources