Help Needed - Select Statement

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

General SQL Server Forums

New to SQL Server Programming

Help Needed - Select Statement

Author

Topic

ZITZAT
Starting Member

6 Posts

Posted - 2012-04-30 : 15:19:20

Hi,

I have a table (tbl1) in the following format

case_id emp_name checked
1 emp 1 Y
2 emp 1 Y
4 emp 1 Y
3 emp 1 Y
253 emp 1 N
5 emp 1 N
6 emp 1 N
7 emp 2 N
8 emp 2 N
9 emp 2 Y
10 emp 2 Y
11 emp 2 Y
12 emp 2 N
13 emp 3 Y
14 emp 3 N
15 emp 3 Y
16 emp 3 N
17 emp 3 Y

Required output is:

emp_name cnt_emp_name Checked_Y
emp 1 7 4
emp 2 6 3
emp 3 5 3

So far I've managed to write:
SELECT emp_name, count(emp_name) as cnt_emp_name FROM tbl1 group by emp_name

This works well and gets me the first 2 columns of deired output. However as far as the third column is concerned, I've not been able to even think in the right direction.

Checked_Y is suppose to provide the count of Y against each emp_name.

!! SOS !!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-30 : 15:22:07

You can use COUNT function with a case expression inside. Or even a SUM function with case expression, like this:

SELECT
	emp_name,
	COUNT(emp_name) AS cnt_emp_name,
	SUM(CASE WHEN Checked = 'Y' THEN 1 ELSE 0 END) AS checked_y
FROM
	tbl1
GROUP BY
	emp_name;

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-04-30 : 15:22:35

[code]SELECT
emp_name,
count(*) AS cnt_emp_name,
SUM(CASE WHEN checked = 'Y' THEN 1 ELSE 0 END) AS Checked_Y
FROM
tbl1
group by
emp_name
[/code]Bah.. too slow :)

ZITZAT
Starting Member

6 Posts

Posted - 2012-04-30 : 15:30:53

Oh Boy! am I at the right place or really at the right place. Amazed by the quick response. You all are great. It felt like it would work but I got an error.

missing operator in query expression 'SUM(CASE WHEN Checked = 'Y' THEN 1 ELSE 0 END)'

and my thanks to all.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-30 : 15:36:10

quote:
Originally posted by ZITZAT

Oh Boy! am I at the right place or really at the right place. Amazed by the quick response. You all are great. It felt like it would work but I got an error.

missing operator in query expression 'SUM(CASE WHEN Checked = 'Y' THEN 1 ELSE 0 END)'

and my thanks to all.

Are you using sql server?
also which is editor you're using?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ZITZAT
Starting Member

6 Posts

Posted - 2012-04-30 : 15:41:43

I am using MS Access in this case. Thanks for leading me in the right direction though. My Cheers to Lamprey and sunitabeck. You both very extremely helpful.

I had to use SUM(IFF(CHECKED='Y',1,0)) instead of 'SUM(CASE WHEN Checked = 'Y' THEN 1 ELSE 0 END)'.

Thanks SQL Team. You rock!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 10:04:12

quote:
Originally posted by ZITZAT

I am using MS Access in this case. Thanks for leading me in the right direction though. My Cheers to Lamprey and sunitabeck. You both very extremely helpful.

I had to use SUM(IFF(CHECKED='Y',1,0)) instead of 'SUM(CASE WHEN Checked = 'Y' THEN 1 ELSE 0 END)'.

Thanks SQL Team. You rock!!

in future please post Access related questions in Access forum to get Access specific solutions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources