Divide a column with the total table row count

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)

Divide a column with the total table row count

Author

Topic

flopdix
Starting Member

3 Posts

Posted - 2011-05-16 : 19:28:29

Hello Friends,
I have a table, lets say customer with columns (CID, status) and data as follows (first column being the CID):

1|0
2|0
3|12
4|2
5|2

To show the number of status for each customer id, i group by status and the query is as follows:

select status, count(*) from customer group by status

this gives me the following result set (status, count):

0|2
12|1
2|2

now the table has total 5 rows, i want to display the result as below:

0|2/5
12|1/5
2|2/5

Can someone tell me how do i write the query to achieve this result set using a single query. Basically if i 'select count(*) from customer' then that gives me the total row count of the table. But still thinking on how to achieve with divide by the total row count for the second column in the result.

Your help is appreciated. Will wait to hear from someone, thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-16 : 19:39:34

Would this work foryou?


select distinct 
   status, 
   count(*) over (partition by status), 
   count(*) over() 
from 
   theTable

flopdix
Starting Member

3 Posts

Posted - 2011-05-16 : 19:43:33

Thanks sunita. I was indeed achieving this using the below query:

select status, count(*), (select count(*) from customer) from customer group by status

I actually wanted to see if i can get the total count combined with the status count in the same column as mentioned for the final resultset.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-16 : 19:47:21

Did you mean like this? The column would necessarily be varchar, of course.

select distinct 
   status, 
   cast(count(*) over (partition by status) as varchar)+'/'+cast(count(*) over() as varchar)
   --count(*) over (partition by status), 
   --count(*) over() 
from 
   theTable

flopdix
Starting Member

3 Posts

Posted - 2011-05-16 : 20:36:02

quote:
Originally posted by sunitabeck

Did you mean like this? The column would necessarily be varchar, of course.
select distinct
status,
cast(count(*) over (partition by status) as varchar)+'/'+cast(count(*) over() as varchar)
--count(*) over (partition by status),
--count(*) over()
from
theTable

Yes sunita. You got it. Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-05-17 : 05:56:15

quote:
Originally posted by sunitabeck

Did you mean like this? The column would necessarily be varchar, of course.
select distinct
status,
cast(count(*) over (partition by status) as varchar)+'/'+cast(count(*) over() as varchar)
--count(*) over (partition by status),
--count(*) over()
from
theTable

When you cast or convert to varchar, always specify length
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-17 : 06:44:28

quote:
Originally posted by madhivanan

When you cast or convert to varchar, always specify length
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

Thanks Madhivanan. I knew all of the information you have in that link, except I had no idea about this magic number 30. How/why they came up with that, I wonder!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-05-17 : 09:04:16

quote:
Originally posted by sunitabeck

quote:
Originally posted by madhivanan

When you cast or convert to varchar, always specify length
http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

Thanks Madhivanan. I knew all of the information you have in that link, except I had no idea about this magic number 30. How/why they came up with that, I wonder!!

My guess is that when you convert datetime to specific regional date format, the maximum characters required is 30 so it is set to 30 by default to accomadate it.

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources