How to get values/counts for multiple columns? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2005 Forums

Transact-SQL (2005)

How to get values/counts for multiple columns?

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2009-12-10 : 11:51:41

Hello -

I am trying to get the values and counts for each value from a table for multiple columns.

I was using a series of UNIONs and was wondering if there was a better approach.


SELECT AccountType AS ColumnType, 'AccountType' AS ColumnSource, Count(*) FROM dbo.TableA GROUP BY AccountType
UNION
SELECT AccountSegment AS ColumnType, 'AccountSegment' AS ColumnSource, Count(*) FROM dbo.TableA GROUP BY AccountSegment 
UNION
SELECT AccountIndustry AS ColumnType, 'AccountIndustry' AS ColumnSource, Count(*) FROM dbo.TableA GROUP BY AccountIndustry 
....etc

This works and give me what i am looking for, but if there is a better/different way of getting to this data, I'd welcome the opportunity to learn something new.

thanks
- will

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-10 : 12:51:30

Those will all return the same answer. I suspect you actually want something like this

SELECT SUM(CASE WHEN ColumnType = 'AccountType' THEN 1 ELSE 0 END)

Post the table structure and expected outlet and I can give you a better answer.

Jim

Everyday I learn something that somebody else already knew

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2009-12-10 : 13:23:27

Sure, here is a subset of the table structure:


CREATE TABLE AccountMstr
(  AccountID int identity(1,1),
   AccountName varchar(100) not null,
   AccountType varchar(50) not null,
   AccountSegment varchar(25) not null,
   AccountIndustry varchar(50) not null,
   AccountCountry varchar(100) not null,
   AccountStartDate datetime not null,
   AccountEndDate datetime null

Expected output is something like:


    Column_Value     Column_Source     Count
    Full Service     Account_Type       1200
    Self Service     Account_Type       8776
    System Level     Account_Type        879
    Financial        Account_Industry   1743
    Retail           Account_Industry   6110
    Platinum         Account_Segment     523
    Silver           Account_Segment    4991

I don't see how the solution you provided would work. Are you suggesting I join to some of the system views (like information_schema.tables/columns) to get to the Column Name or ???

thanks
- will

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-10 : 13:54:24

your original method seems the easiest.

Just to get column 1 in your desired results would require unioning the distinct names.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-12-10 : 14:00:22

you can also use UNPIVOT to get this

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources