INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Select count from multiple col's

Select count from multiple col's

(OP)
I have this table like the one below. The top row has col names.

I need a result of a count of all the "y"'s in each col but am not sure how to do it.
I'd rather do it on the MS SQL end than pull it into a dataset and work it.
I can do one col at a time but it would create 9 hits on the db.
I'd like to grab it all in one statement.

Thanks

-dan


Gall Sall g1 g2 s1 s2 s3 s4 s5
y y n n n n n n n
y y n n n n n n n
n y y n n n n n n
y y n n n n n n n
n n n y y y n n y
y n n y n n n y y
y y n n n n n n n
y n n n n n n n n
y y n n n n n n n
y y n n n n n n n
y y n n n n n n n
y y n n n n n n n

RE: Select count from multiple col's

CODE

Select Count(Case When Gall = 'Y' Then 1 Else NULL End) As GallCount,
       Count(Case When Sall = 'Y' Then 1 Else NULL End) As SallCount
       etc....
From   YourTableName 

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Select count from multiple col's

Or

SELECT
(SELECT COUNT(Gall) FROM tbl WHERE (Gall = 'y')) AS Count_Gall,
(SELECT COUNT(Sall) FROM tbl WHERE (Sall = 'y')) AS Count_Sall,
(SELECT COUNT(g1)   FROM tbl WHERE (g1 = 'y'))   AS Count_g1,
...
 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Select count from multiple col's

Or you unpivot that data:

CODE

Select u.ColumnName, Count(*) As CountYes 
From yourtable
Unpivot (YesNo For ColumnName In (Gall,	Sall, g1, g2, s1, s2, s3, s4, s5)) u
Where YesNo = 'y'
group by ColumnName

--or

Select u.ColumnName, u.YesNo, Count(*) 
From yourtable
Unpivot (YesNo For ColumnName In (Gall,	Sall, g1, g2, s1, s2, s3, s4, s5)) u
group by ColumnName, YesNo 

Bye, Olaf.

RE: Select count from multiple col's

(OP)
Thanks all

I had a subquery style statement working. It just seemed like a very long statement. Guess I was on the right track but just with some doubt.

@Olaf that's the first time I've seen the unpivot function. I'll have to go study that one to get my head wrapped around it.

Really appreciate the responses.

thanks again

-dan


RE: Select count from multiple col's

A definition of unpivoting as inverse to pivoting won't perhaps won't help and tell you what it is, but you see the way you have your data is not ideal for counting, though what you want to count is in rows.

This query is tested with test data having these columns, but you say these column names are just the first line of data. Well, you need the real column names for unpivoting to work.

To see unpivots effect just do:

CODE

Select * 
From yourtable
Unpivot (YesNo For ColumnName In (Gall,	Sall, g1, g2, s1, s2, s3, s4, s5)) u 

A simple self conatained (running as is) example:

CODE

declare @data as Table (Row int, Col1 char(4), Col2 char(4))
insert into @data values (1,'r1c1','r1c2'),(2,'r2c1','r2c2');

Select u.*
From @data
Unpivot (Cell For Col In (col1, col2)) u 

The result has a row for each cell, so to say. You get three columns row (inherited), Cell and Col (both from the unpivot operation).
The column names of the table become data of the new Col column, so Col's values are 'col1' or 'col2', in short this is how this unpivoted data looks like:



I intentionally put the values r1c1 to r2c2 into the initial data, these 2x2 cell names now occur each in its own record. In your case this column would be the YesNo column only containing Ys and Ns. Row is optional, but you see this data simply grouped and groups counted, you can get the Y counts only or both Y an d N and whatever other values you have, you can decide for overall count or per column name, etc.

So the Unpivoted data mainly has the cell values (the values of the initial table columns) in one record each, together with the name of the column it came from. I have the strong feeling your original data is the result of a pivoting of data and you already have that unpivoted data as a previous step. For SQL Server it is much more convenient to have similar data (like your 'y' and 'n') in one record each to count them, so you may rather only need to go one step back and do just the simple counting query with a group by two or more columns.

The usual case is actually reverse, people start with atomic data and want to convert them into a table with columns for each person, month or whatever category, as you might guess and as initially said T-SQL also offers that via Pivot.

Bye, Olaf.

RE: Select count from multiple col's

What's interesting here is that the solution I presented performs better than all the others (even the unpivot version). It's super simple and very straightforward.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Select count from multiple col's

I always detested getting a report, especially a pivot. Usually this was a periodic thing that needed massaging each time.

First order of business: find the source and get access to a proper table(s). If that wasn't possible, then normalize, cuz inevitably the powers that be wanted other related stats. Of course, the limitation with normalization is that you can't get further Granularity. But normalization beats some one of a kind aggregation program, IMNSHO.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Select count from multiple col's

Unless you program a code generator I would still prefer a simpler syntax of unpivoting to a list of count(case). I also could cope better with multiple result rows (one per count) than one record having all the counts in one row.

Let me think about a real world problem having y/n answers. Say n people choose from m dates, you want to find the date to which most people can say yes, in the form of
person day1 day2 day3
   1     y    y    n
   2     y    n    y 
   3     y    n    y
 

So you ask for

CODE

Select Top 1 [Date], Count(*) As CountYes 
From yourtable
Unpivot (CanAttend For Date In (day1, day2, day3)) u
Where CanAttend = 'y'
Group by [Date]
Order By CountYes Desc 

Anyway, you wouldn't have this form of data, you would start with data like

person canattendonday
   1        1
   1        2
   2        1
   2        3
   3        1
   3        3    
 

And that would only need a query:

CODE

Select canattendonday as Day, 
count(*) as AvailbleAttendeesOnThatDay 
From thattable 
Group By canattendonday
Order By AvailbleAttendeesOnThatDay Desc 

I don't assume the initial data, the data Dashley has before he has the posted y/n table, is rather in such a form and you then don't need to unpivot something. That's why I said "I have the strong feeling your original data is the result of a pivoting of data". Go back to the previous data and the counting query gets much simpler.

Bye, Olaf.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close