help inSUM - 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

help inSUM

Author

Topic

blindmancries
Starting Member

3 Posts

Posted - 2006-01-30 : 07:06:55

I need some help regarding SQL Query.

I have a SQL Server databse a big one with a lot of records with table in like:

EMBG Name Surname Total date
1. 2712 Naim Arifi 70 2/23/2005
2. 2563 xxx yyyy 85 5/9/2005
3. 2492 aaa bbb 563 7/17/2005
4. 2712 Naim Arifi 79 12/25/2005
5. 2712 Naim Arifi 123 1/10/2006

I need a query who will count how many times for example Naim Arifi is like a record for a period of time. Let say I want to find from January 1-st 2005 till today.

Another one is that I need to SUM the total for one person. for example Naim Arifi With total 272.

Because the db is big one, it should look like this:

First case:

1. 2712 Naim Arifi 3
2. 2563 xxx yyyy 1
3. 2492 aaa bbb 1

And, Second case:

1. 2712 Naim Arifi 272
2. 2563 xxx yyyy 185
3. 2492 aaa bbbb 322
.
.
.
.
.
.
12564. vvv vvv 782

I really will apprepriate this if someone will help me regarding this problem.

Thanks in advice,
N.A

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-30 : 07:45:07

Use Group By

select 	Name, Surname, count(*) 
from 	yourtable
group by Name, Surname

select 	Name, Surname, sum(Total)
from 	yourtable
group by Name, Surname

----------------------------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-30 : 07:56:33

Also Learn SQL
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

blindmancries
Starting Member

3 Posts

Posted - 2006-01-30 : 09:25:03

Yeah it is OK. Query works.
I have made like this

select Name, Surname, sum(total)"totali", count(*)"nr"
from tbl
group by Name, Surname

How can I sort them starting from the biggest one. In Descending form.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-30 : 09:34:25

Select * from
(
select Name, Surname, sum(total)"totali", count(*)"nr"
from tbl
group by Name, Surname
) T
order by nr DESC

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2006-01-30 : 09:39:10

Look at that, the blind dude has a relative

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-30 : 09:45:47

Looks like a long distance relative