counting and total - 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

General SQL Server Forums

New to SQL Server Programming

counting and total

Author

Topic

unikoman
Starting Member

32 Posts

Posted - 2006-06-10 : 06:42:20

Hi All

I have:

select prod_id , count(prod_id)[Number of Times]
from orders_tbl
group by prod_id
order by 1;

I get a result however, how do sum up the total number of times ?

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-10 : 06:49:15

Use Derived table


Select prod_id, sum([Number of Times]) as Summation from
(
         select prod_id , count(prod_id)[Number of Times]
         from orders_tbl
         group by prod_i
) T
group by prod_i
order by 1

Madhivanan

Failing to plan is Planning to fail

unikoman
Starting Member

32 Posts

Posted - 2006-06-10 : 06:59:41

Thanks Madhivanan

your query gives me the same result as the one I posted:

Prod_id Summation
11235 1
13 1
222 2
90 1
907 1

what I want is to add up the values in the Summation column, 1+1+2+1+1= 6 and display it in the Total result..... how do I get this ?

Thanks in advance

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-10 : 07:09:52

Remove group by

Select sum([Number of Times]) as Summation from
(
         select prod_id , count(prod_id)[Number of Times]
         from orders_tbl
         group by prod_i
) T

Madhivanan

Failing to plan is Planning to fail

unikoman
Starting Member

32 Posts

Posted - 2006-06-10 : 08:01:19

I can't get that to work........
Column 'T.prod_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-10 : 09:05:04

Read my previous reply. I didnt use prod_id in select statement

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources