grouping and counting a column

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

Transact-SQL (2008)

grouping and counting a column

Author

Topic

crazyco
Starting Member

30 Posts

Posted - 2010-09-02 : 16:56:05

I had 2 columns which I had to merge into one, this I have managed to do but now I need to group together the data in the column and count each group. Can anyone advise on how to do this?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-02 : 17:33:34

SELECT <yourcolumn>, count(*)
FROM <yourTable>
GROUP BY <yourcolumn>

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-09-02 : 17:43:27

BTW, if you hadn't merged the 2 columns in to one column, this query would produce the results you want
SELECT <yourcolumn>, <yourothercolumn>, count(*)
FROM <yourTable>
GROUP BY <yourcolumn>, <yourothercolumn>

Jim

Everyday I learn something that somebody else already knew

crazyco
Starting Member

30 Posts

Posted - 2010-09-03 : 03:36:20

These didn't work for me, I joined my columns 2 different ways, i.e.:

SELECT col
FROM
(SELECT <yourcolumn>, <yourothercolumn>,
FROM <yourTable>) all
UNPIVOT
(col FOR xxx IN
(<yourcolumn>, <yourothercolumn>, )
)AS unpvt

select * from <yourTable>
SELECT <yourcolumn>, FROM <yourTable>
UNION ALL
SELECT <yourothercolumn>, FROM <yourTable>
order by <yourcolumn>

but couldn't get the count to work on either of these, so I also tried:

SELECT Count(<yourcolumn>), <yourcolumn>
FROM <yourtable>
GROUP BY <yourcolumn>
UNION all
SELECT Count(<yourothercolumn>), <yourothercolumn>
FROM <yourtable>
GROUP BY <yourcolumn>, <yourothercolumn>

which is half way there but still not correct.

crazyco
Starting Member

30 Posts

Posted - 2010-09-03 : 06:14:37

I have figured it out, this worked:

select <newColumn>, count(*)
from
(select <yourtable> as <newColumn> from <yourTable>
union all
select <yourothercolumn> as <yourothercolumn> from <yourTable>) as <newColumn>
where <newColumn> is not null
group by <newColumn>
order by <newColumn>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources