Select count from multiple col's

[Dashley]

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

 

[gmmastros]

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

 

[Andrzejek]

Or

[pre]
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,
...
[/pre]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Olaf Doschke]

Or you unpivot that data:

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.

 

[Dashley]

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

 

[Olaf Doschke]

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:

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:

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.

 

[SkipVought]

HI,

If you so choose, you can export your data as .csv and import to Excel. Then use this nifty 'trick' to normalize (un pivot) your data for analysis.

Faq68-5287.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[gmmastros]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Olaf Doschke]

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
[pre]
person day1 day2 day3
1 y y n
2 y n y
3 y n y
[/pre]

So you ask for

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

[pre]person canattendonday
1 1
1 2
2 1
2 3
3 1
3 3
[/pre]

And that would only need a query:

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.