sum total of rows - 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

SQL Server 2000 Forums

Transact-SQL (2000)

sum total of rows

Author

Topic

Ats
Starting Member

32 Posts

Posted - 2009-02-05 : 13:20:50

I have run the following query and it is almost correct

the result i get is (i have added countrycode to show what i think is causing the problem) my results show 2 rows i just want the sum of cer in 1 row can anybody help?

company_id cer year countrycode
1 147805.50 2008 AT
1 75944.00 2008 CZ

Query

SELECT compliance.company_id, (Sum(compliance.allowancedistributed*5) * (cer.cerallowance/100.0)) as cer, compliance.year1, cer.countrycode
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
GROUP BY compliance.Company_ID, cer.cerallowance, compliance.year1, cer.countrycode
HAVING compliance.Company_ID = 1 and compliance.year1 = '2008';

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-05 : 13:28:44

remove countrycode from select and group by clause then.

Ats
Starting Member

32 Posts

Posted - 2009-02-05 : 13:30:09

I did that originally and iget the same 2 rows without the country code

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-05 : 13:30:29

How can you implement Country Code in 1 row? Are you looking for Comma separated for it?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-05 : 13:32:16

remove cerallowance also, The 2 rows must be having different cerallowance.... thats why they show as 2 rows.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-05 : 13:33:07

remove cerallowance from the group by clause I mean.

Ats
Starting Member

32 Posts

Posted - 2009-02-05 : 13:34:14

All I want is the sum of the 2 rows 147805.5 + 75944 = 223749.5 but it wont work

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-05 : 13:35:05

quote:
Originally posted by sakets_2000

remove cerallowance from the group by clause I mean.

How will that give 1 row for Cer?

Ats
Starting Member

32 Posts

Posted - 2009-02-05 : 13:36:43

I removed the cer allowance statement and go the error

Msg 8120, Level 16, State 1, Line 1
Column 'CER.CERAllowance' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT compliance.company_id, (Sum(compliance.allowancedistributed*5) * (cer.cerallowance/100.0)) as cer, compliance.year1
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
GROUP BY compliance.Company_ID, compliance.year1
HAVING compliance.Company_ID = 1 and compliance.year1 = '2008';

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-05 : 13:36:57

quote:
Originally posted by sodeep

quote:
Originally posted by sakets_2000

remove cerallowance from the group by clause I mean.

How will that give 1 row for Cer?

Obviously cerallowance & countrycode differ for the 2 rows, Not grouping them by the 2 fields would sum up the values.

Ats
Starting Member

32 Posts

Posted - 2009-02-05 : 13:39:11

yes saskats the cer allowance are different for both rows but I want it to add the 2 rows up after this

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-05 : 13:39:13

quote:
Originally posted by sakets_2000

quote:
Originally posted by sodeep

quote:
Originally posted by sakets_2000

remove cerallowance from the group by clause I mean.

How will that give 1 row for Cer?

Obviously cerallowance & countrycode differ for the 2 rows, Not grouping them by the 2 fields would sum up the values.

Yes that is obvious but OP is looking for Countrycode as well . Right.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-05 : 13:40:01

quote:
Originally posted by sodeep

quote:
Originally posted by sakets_2000

quote:
Originally posted by sodeep

quote:
Originally posted by sakets_2000

remove cerallowance from the group by clause I mean.

How will that give 1 row for Cer?

Obviously cerallowance & countrycode differ for the 2 rows, Not grouping them by the 2 fields would sum up the values.

Yes that is obvious but OP is looking for Countrycode as well . Right.

So OP modified the requirement then.

Ats
Starting Member

32 Posts

Posted - 2009-02-05 : 13:40:40

so deep the country code is needed so it can reference the cer allowance

Ats
Starting Member

32 Posts

Posted - 2009-02-05 : 13:46:04

I have changed the formula to show what I get
company_id cer year
1 147805.5 2008
1 75944.0 2008

SELECT compliance.company_id, (Sum(compliance.allowancedistributed*5) * (cer.cerallowance/100.0)) as cer, compliance.year1
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
GROUP BY compliance.Company_ID, compliance.year1, cer.cerallowance
HAVING compliance.Company_ID = 1 and compliance.year1 = '2008';

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-05 : 13:46:15

I am guessing you are making a mistake while calc cer,
You sure you dont want this?? Look at the change I made..

SELECT compliance.company_id, (Sum(compliance.allowancedistributed*5 * cer.cerallowance/100.0)) as cer, compliance.year1
FROM compliance INNER JOIN (installation INNER JOIN CER ON installation.countryCode = CER.countryCode) ON compliance.permitIdentifier = installation.permitIdentifier
GROUP BY compliance.Company_ID, compliance.year1 
HAVING compliance.Company_ID = 1 and compliance.year1 = '2008';

Ats
Starting Member

32 Posts

Posted - 2009-02-05 : 13:50:07

Hey saskets it seems what ever change you made worked, I have been baffled on that for a couple days now. thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources