Count Data From Different Columns

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

Count Data From Different Columns

Author

Topic

Patrickssj6
Starting Member

4 Posts

Posted - 2009-10-06 : 14:06:17

Basically I have 3 Columns(Fach1,Fach2,Fach3). I want to count all items that meet a certain condition (WHERE Fach = 'M') in all three columns.

I tried many different things but I cannot get it to work.

Thank you in advance!

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-06 : 14:06:50

what have you tried...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Patrickssj6
Starting Member

4 Posts

Posted - 2009-10-06 : 14:08:41

Some things with Group By or SELECT COUNT(Fach1) And COUNT(Fach2)...I am new to this.

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-06 : 14:09:17

how about


SELECT    SUM(CASE WHEN Fach1 = 'M' THEN 1 ELSE 0) AS Fach1_Count_M
		, SUM(CASE WHEN Fach2 = 'M' THEN 1 ELSE 0) AS Fach2_Count_M
		, SUM(CASE WHEN Fach3 = 'M' THEN 1 ELSE 0) AS Fach3_Count_M
  FROM myTable99

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Patrickssj6
Starting Member

4 Posts

Posted - 2009-10-06 : 14:12:45

Select * from lehrer

SELECT SUM(CASE WHEN Fach1 = 'M' THEN 1 ELSE 0) AS Fach1_Count_M
, SUM(CASE WHEN Fach2 = 'M' THEN 1 ELSE 0) AS Fach2_Count_M
, SUM(CASE WHEN Fach3 = 'M' THEN 1 ELSE 0) AS Fach3_Count_M
FROM Lehrer

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.

Hmm...sorry I am not someone that wants to get spoon fed but I am too inexperienced in SQL :P

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-06 : 14:13:57

quote:

Originally posted by X002548

how about


SELECT    SUM(CASE WHEN Fach1 = 'M' THEN 1 ELSE 0 END) AS Fach1_Count_M
		, SUM(CASE WHEN Fach2 = 'M' THEN 1 ELSE 0 END) AS Fach2_Count_M
		, SUM(CASE WHEN Fach3 = 'M' THEN 1 ELSE 0 END) AS Fach3_Count_M
  FROM myTable99

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-06 : 14:14:46

I'm assuming that Brett's solution works for you, but here are some other ways to count:

DECLARE @Foo TABLE (Fach1 CHAR(1), Fach2 CHAR(1), Fach3 CHAR(1))

INSERT @Foo
SELECT 'A', 'B', 'C'
UNION ALL SELECT 'M', 'D', 'E'
UNION ALL SELECT 'G', 'H', 'I'
UNION ALL SELECT 'J', 'M', 'K'
UNION ALL SELECT 'M', 'M', 'M'
UNION ALL SELECT 'N', 'O', 'P'

SELECT 
	SUM(CASE WHEN 'M' IN (Fach1,Fach2,Fach3) THEN 1 ELSE 0 END) AS RowWiseSum,
	SUM(CASE WHEN Fach1 = 'M' THEN 1 ELSE 0 END
		+ CASE WHEN Fach2 = 'M' THEN 1 ELSE 0 END
		+ CASE WHEN Fach3 = 'M' THEN 1 ELSE 0 END
		) AS ColumnWiseSum
	
FROM @Foo

Patrickssj6
Starting Member

4 Posts

Posted - 2009-10-06 : 14:18:58

I combined your two codes because this is what I wanted in the end:
SELECT SUM(CASE WHEN Fach1 = 'M' THEN 1 ELSE 0 END
+ CASE WHEN Fach2 = 'M' THEN 1 ELSE 0 END
+ CASE WHEN Fach3 = 'M' THEN 1 ELSE 0 END
) AS MatheLehrer
FROM Lehrer

Thanks a bunch guys!

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-06 : 14:47:53

I forgot END

What a scrub

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-10-06 : 15:10:56

quote:
Originally posted by X002548

I forgot END

What a scrub

Bush league Brett!! ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources