Help with adding up values in multipul rows

Question

Hi All,

I am trying to write a select statement that will allow me to add together the values from the rows returned in my query. For example, I am trying to identify the amount of pages printed by a perticular user. The query I am using is:

select pagesprinted,username from myprintdb where username='jbloggs'

The result I get back is (for example):

PagesPrinted      Username
2                       jbloggs
7                       jbloggs
3                       jbloggs

What I would like it to return is

PagesPrinted      Username
12                     jbloggs

This way I can do a "top 10" and try to find out our heaviest users.

Any help is appreciated

Thanks
Matt

Answer 1

This way I can do a "top 10" and try to find out our heaviest users.

Try with:

SELECT top 10 username,sum(pagesprinted)
from myprintdb
group by username
order by SUM(pagesprinted) desc

Answer 2

Hi Matt,

You can use GROUP BY clause to aggregate data.

select
	sum([pagesprinted])
  , [username]
from
	[myprintdb]
where
	[username] = 'jbloggs'
group by
	[username]

The above query just sums up the field [pagesprinted] for each different [username].

Regards

Padmanabhan

Answer 3

May also want to look into ranking functions to optimize your return for "top 10" type situations.  It typically willl give you more accurate TOP usage details for instances like these.

e.g.

Create Table PageEnum(PagesPrinted int,Username varchar(200))
insert into PageEnum Values (2,'jbloggs6')
insert into PageEnum Values (7,'jbloggs3')
insert into PageEnum Values (3,'jbloggs9')
insert into PageEnum Values (42,'jbloggs1')
insert into PageEnum Values (6,'jbloggs2')
insert into PageEnum Values (2,'jbloggs1')
insert into PageEnum Values (82,'jbloggs44')
insert into PageEnum Values (71,'jbloggs111')
insert into PageEnum Values (3,'jbloggs9')

create index idx_Username_nonclust on PageEnum(Username)

Select 
	PageCounts.Totals
	,PageCounts.Username
	,Ranking
From
	(
	select
		RANK() OVER (ORDER BY Sum(PagesPrinted) DESC) AS Ranking
		,Sum(PagesPrinted) Totals
		,Username
	from PageEnum
	Group By Username
	) PageCounts
Where Ranking <= 5

---

Ted Krueger Blog on lessthandot.com @onpnt on twitter