Unable to make query with SUM function

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 2000 Forums

Transact-SQL (2000)

Unable to make query with SUM function

Author

Topic

RaxSuur
Starting Member

7 Posts

Posted - 2005-08-19 : 02:09:18

Hai. I'm very bad at sql, but I'm making a little project here.

I need to do query from a table:

Table:
Col A--Col B--Col C
12-----6------0
12-----2------1440
12-----4------0
12-----7------1390
13-----3------2540
13-----5------3200
13-----4------0
13-----5------4250
14-----8------2500
14-----9------2420
14-----8------0
14-----2------0

And query should be like this:
Col A--SumOf Col B--Col C
12-----19-----------1440
12-----19-----------1390

Can somebody help me please?
13-----17-----------2540
13-----17-----------3200
13-----17-----------4250
14-----27-----------2500
14-----27-----------2420

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-19 : 02:18:47

Try this

Select G.ColA,G.sumB, ColC from yourTable T
inner join (Select colA, sum(ColB) as sumB from yourTable
group by ColA) G on T.colA=G.colA and T.ColC<>0

Madhivanan

Failing to plan is Planning to fail

RaxSuur
Starting Member

7 Posts

Posted - 2005-08-19 : 02:37:49

Sorry, my text is on the table. I'll post it again

Table:
Col A--Col B--Col C
12-----6------0
12-----2------1440
12-----4------0
12-----7------1390
13-----3------2540
13-----5------3200
13-----4------0
13-----5------4250
14-----8------2500
14-----9------2420
14-----8------0
14-----2------0

And query should be like this:
Col A--SumOf Col B--Col C
12-----19-----------1440
12-----19-----------1390
13-----17-----------2540
13-----17-----------3200
13-----17-----------4250
14-----27-----------2500
14-----27-----------2420

Important is not to SUM Col C, I must see all rows where Col C > 0

Thanks to Madhivanan, but this is not exactly what I need.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-19 : 02:42:25

Well

I edited my posts and it seems you didnot see that

I post my edited query here

Select G.ColA,G.sumB, ColC from yourTable T
inner join (Select colA, sum(ColB) as sumB from yourTable
group by ColA) G on T.colA=G.colA and T.ColC<>0

Madhivanan

Failing to plan is Planning to fail

RaxSuur
Starting Member

7 Posts

Posted - 2005-08-19 : 03:09:12

Well, perhaps your SQL statement is right, but I'm working in Access and it says that JOIN expression not supported.

Thank You for help. I must figure out something else.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-19 : 04:17:21

Well

This should work in Access

Select G.ColA,G.sumB, ColC from yourTable T
inner join (Select colA, sum(ColB) as sumB from yourTable
group by ColA) G on T.colA=G.colA where T.ColC<>0

Madhivanan

Failing to plan is Planning to fail

RaxSuur
Starting Member

7 Posts

Posted - 2005-08-19 : 04:41:30

OK.

It also works without JOIN.

I got it from another forum:

select a.colA, b.colX, a.colC
from t1 a, (select colA,sum(colB) colX from t1 group by colA) b
where a.colA = b.colA and a.colC <> 0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-19 : 04:51:09

Yes
It is derived table concept which does the same functionality

Madhivanan

Failing to plan is Planning to fail

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-08-19 : 05:52:21

Two queries are equivalent, second one just uses the old join syntax that should be avoided.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-19 : 05:58:50

quote:
Originally posted by mmarovic

Two queries are equivalent, second one just uses the old join syntax that should be avoided.

mmarovic, will not that old join supported in newer versions?

Madhivanan

Failing to plan is Planning to fail

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-08-19 : 06:13:10

I don't know, but I hope it will be dropped, the sooner the better.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources