Using Aggregate - 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

Using Aggregate

Author

Topic

ann06
Posting Yak Master

171 Posts

Posted - 2008-03-24 : 05:37:31

I have a table that has 4 colums (id,projectno,date,price)
i want to make a select that returns the sum per project no
i used this query
select projectno,sum(pice) as sum
from supplier
group by projectno

but i want to include additional columns like id and date for the result
but its giving this message:
Column 'supplier.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

is there a better way to do so without joining the main table with the upper select query?
Best Regards

ranganath
Posting Yak Master

209 Posts

Posted - 2008-03-24 : 05:47:00

Hi

try with this

select id, date , projectno, sum(pice) as sum
from supplier
group by id, date, projectno

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-03-24 : 05:47:51

You are trying to "Group" which means for example

id, Project, Date, Price
1, A,10-12-2007,100
2, A,11-12-2007,200

Should give a result of

A-300 which means that the total price for project A is 300.

In the above example, you have 2 diffrent date and Id's for the same proj and thats why it is not allowing you to select them. Now based on the above example, can you explain what is you need?

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-03-24 : 05:48:57

quote:
Originally posted by ranganath

Hi

try with this

select id, date , projectno, sum(pice) as sum
from supplier
group by id, date, projectno

Ranganath, you query is same as

select * from supplier

or

select id, date, projectno,price from supplier

ann06
Posting Yak Master

171 Posts

Posted - 2008-03-24 : 06:36:20

in Reply to Mr.pravin14u
I want my result to be like this

id, Project, Date, Price,Sum
1, A,10-12-2007,100,300
2, A,11-12-2007,200,300

so that is displays the columns information and the total sum of the prices
Thnxxx for the replies its my first participation here i like this this its very useful to me

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-24 : 06:39:32

[code]select
t1.id, t1.date , t1.projectno, t2.sum_price as [Sum]
from supplier t1 join
(select projectno,sum(pice) as sum_price
from supplier
group by projectno ) t2 on t1.projectno = t2.projectno[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-03-24 : 07:16:39

If you are using SQL 2005 try this

Declare @T Table (id Int, Project Varchar(10), Date DateTime, Price Int)

Insert into @T
Select 1, 'A','10-12-2007',100 Union All
Select 2, 'A','11-12-2007',200

Select *, Sum(Price) Over(Partition By Project) 'Sum'
From @t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources