sql sum query help - 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

sql sum query help

Author

Topic

rwtrwt2003
Starting Member

3 Posts

Posted - 2014-12-01 : 14:45:52

I got 2 database. MailingLIst has the name that i want to query in CellLoad. In CellLoad has all the data that i want to sum.
example of MailingList
id vendor switch postion email area region

CellLoad
id switch Date_Start Put_on_Schedule total1 event

My sql query
[sql]
SELECT
Sum(CASE WHEN(`CellLoad`.`Event` = 'Cell' AND `CellLoad`.`Switch` = `MailingList`.`Switch` ) THEN Total1 ELSE 0 END) AS Tcell,
FROM CellLoad,MailingList
WHERE YEAR(`CellLoad`.`Date_Start`) = '2014' AND `MailingList`.`Area` = 'WEST' AND `CellLoad`.`Put_on_Schedule` = ('Email' or 'Done')
[/sql]

The problem is that the output is more then what is in the database.
The Sum is not right. If i take out the `CellLoad`.`Switch` = `MailingList`.`Switch` that number is right for all that event that is in the CellLoad database. But i need to have the mailinglist switches for that area just to be added

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-01 : 14:51:54

How are these tables related to each other? It looks like your query is doing a CROSS JOIN since I don't see a JOIN condition. Add one to resolve this issue. I can't tell from your schema what to JOIN on.

And please use the ANSI syntax.

select ...
from t1
join t2
on t1.c1 = t2.c1

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

rwtrwt2003
Starting Member

3 Posts

Posted - 2014-12-01 : 15:14:25

sorry new to the fourm.

I have 2 tables 1 has all the data that i want to sum and the other has the name that i need to query against.

I have tried to us the join and i get a error
here is the code that i am using

SELECT `CellLoad`.`Switch`,
Sum(CASE WHEN (`CellLoad`.`Event`='Cell') Then Total1 ELSE 0 END) as totalC
From `CellLoad`
Where YEAR(`CellLoad`.`Date_Start`) = '2014' AND`CellLoad`.`Put_on_Schedule` = ('Email' or 'Done')
JOIN `MailingList`
ON `CellLoad`.`Switch` = `MailingList`.`Switch`
WHERE `MailingList`.`Area` = 'WEST' AND `MailingList`.`Emailaddress` = '123@321.com'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-01 : 15:28:52

SELECT `CellLoad`.`Switch`,
Sum(CASE WHEN (`CellLoad`.`Event`='Cell') Then Total1 ELSE 0 END) as totalC
From `CellLoad`
JOIN `MailingList`
ON `CellLoad`.`Switch` = `MailingList`.`Switch`
WHERE `MailingList`.`Area` = 'WEST' AND `MailingList`.`Emailaddress` = '123@321.com'
and YEAR(`CellLoad`.`Date_Start`) = '2014' AND`CellLoad`.`Put_on_Schedule` = ('Email' or 'Done')

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

rwtrwt2003
Starting Member

3 Posts

Posted - 2014-12-01 : 15:50:04

So this works the problem is that is not given me the right sum.
I get 67552 as the total
If i take out the MailingList in the sql query i get 19545. This number is correct for the sum in the total1 column of the CellLoad table. But when i add the MailingList option to the sql query the number is bigger
any ideas

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-01 : 16:08:43

You'll need to provide sample data at this point, from both tables, and the output, pointing out where the issue is.

You might just need to add a GROUP BY. It's hard to tell from the info you've given.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources