Counting values in a table - SQL Server Forums

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

Development Tools

Reporting Services Development

Counting values in a table

Author

Topic

enzo_p
Starting Member

7 Posts

Posted - 2008-10-17 : 09:50:40

Hi,

I want to have a table which displays the number of times a value appears in a particular column.

I want to be able to do a COUNT of these values, but without having to supply the WHERE parameter (as the values will change over time)

e.g. if my dbo table has this column:

COL1
A
B
C
A
B

I want to add have a table in my report which would show:

Val Count
--- -----
A 2
B 2
C 1

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-17 : 09:52:41

SELECT COL1 AS Val,COUNT(*) AS [Count]
FROM YourTable
GROUP BY COL1

enzo_p
Starting Member

7 Posts

Posted - 2008-10-17 : 10:00:51

quote:
Originally posted by visakh16

SELECT COL1 AS Val,COUNT(*) AS [Count]
FROM YourTable
GROUP BY COL1

Thanks for your response.

When performing this SQL statement I get a column called Count but as follows:

COL1 Count
---- -----
A 1
B 1
C 1
A 1
B 1

how do i then convert this in Report Server to show me a table with only one oeach value in COL1 with the sum of the count?

enzo_p
Starting Member

7 Posts

Posted - 2008-10-17 : 10:02:59

I should also say that I want be able to add further columns to the right of this table in the report layout, so a Matrix might be a tricky choice here. Hence why I ask about tables.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-17 : 10:03:59

quote:
Originally posted by enzo_p

quote:
Originally posted by visakh16

SELECT COL1 AS Val,COUNT(*) AS [Count]
FROM YourTable
GROUP BY COL1

Thanks for your response.

When performing this SQL statement I get a column called Count but as follows:

COL1 Count
---- -----
A 1
B 1
C 1
A 1
B 1

how do i then convert this in Report Server to show me a table with only one oeach value in COL1 with the sum of the count?

can you show actual query please/ i dont think you will get all counts as one.

enzo_p
Starting Member

7 Posts

Posted - 2008-10-17 : 10:14:43

SELECT assignee_name AS Val, COUNT(*) AS assignee_count, number
FROM TBL1
GROUP BY assignee_name, number

Apologies - I think the problem here is that I am also extracting other columns in the same SQL statement and with them in the GROUP BY it causes the count values to be 1.

Any suggestions?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-17 : 10:28:46

quote:
Originally posted by enzo_p

SELECT assignee_name AS Val, COUNT(*) AS assignee_count, number
FROM TBL1
GROUP BY assignee_name, number

Apologies - I think the problem here is that I am also extracting other columns in the same SQL statement and with them in the GROUP BY it causes the count values to be 1.

Any suggestions?

yup thats the problem adding extra fields to GROUP BY affects count. if you're using sql 2005, you can do like this

SELECT     assignee_name AS Val, COUNT(*) OVER(PARTITION BY assignee_name) AS assignee_count, number
FROM       TBL1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources