April 9, 2013 at 12:04 pm
Hello all, i am having a problem trying to get the sum of a column if a join happens. Basically, i want to sum up a quantity for all rows in table A that can join to table B. the problem is when there are two or more rows in table B, the quantity sum doubles or triples. for instance, in the following setup:
create table playground.sale (email varchar(255), quantity int);
insert into sale values ('email1', 1);
insert into sale values ('email2', 2);
insert into sale values ('email3', 3);
insert into sale values ('email4', 4);
create table eval (server_id varchar(255), email varchar(255));
insert into eval values ('ServerID1', 'email1');
insert into eval values ('ServerID2', 'email1');
insert into eval values ('ServerID3', 'email1');
insert into eval values ('ServerID6', 'email1');
select sum(s.quantity)
from sale s
inner join eval e
on s.email = e.email
where s.email = 'email1'
So, the above will sum to 4. but i only want it to sum to 1. is there a way to get this desired result using a join? i know i can get the result using a 'where s.email in (select email from eval), but that is taking forever and i'm looking to optimize. Thanks in advance for your help.
April 9, 2013 at 12:10 pm
bagofbirds-767347 (4/9/2013)
Hello all, i am having a problem trying to get the sum of a column if a join happens. Basically, i want to sum up a quantity for all rows in table A that can join to table B. the problem is when there are two or more rows in table B, the quantity sum doubles or triples. for instance, in the following setup:create table playground.sale (email varchar(255), quantity int);
insert into sale values ('email1', 1);
insert into sale values ('email2', 2);
insert into sale values ('email3', 3);
insert into sale values ('email4', 4);
create table eval (server_id varchar(255), email varchar(255));
insert into eval values ('ServerID1', 'email1');
insert into eval values ('ServerID2', 'email1');
insert into eval values ('ServerID3', 'email1');
insert into eval values ('ServerID6', 'email1');
select sum(s.quantity)
from sale s
inner join eval e
on s.email = e.email
where s.email = 'email1'
So, the above will sum to 4. but i only want it to sum to 1. is there a way to get this desired result using a join? i know i can get the result using a 'where s.email in (select email from eval), but that is taking forever and i'm looking to optimize. Thanks in advance for your help.
Why would the sum of four values (all 1) be 1? What if you substituted email2 for email1 in your data and query, what would be the result?
What if your sample data for evail and data for 'email1", 'email2', 'email3' and 'email4' and you had no WHERE clause in your query filtering out one email value?
April 9, 2013 at 12:24 pm
thanks for the reply. let me see if i can clarify:
Why would the sum of four values (all 1) be 1?
- for 'Email1' the quantity = 1 in the sale table. i don't want it to be multiplied by the number of 'Email1' rows in the eval table.
What if you substituted email2 for email1 in your data and query, what would be the result?
- if 'email2' where in the where clause 'where s.email = 'Email2', i would expect the sum(quantity) to = 0, since the join condition won't be met.
What if your sample data for evail and data for 'email1", 'email2', 'email3' and 'email4' and you had no WHERE clause in your query filtering out one email value?
- i'm not exactly sure what you are asking. but if there were no WHERE clause, i would expect the same output, as only 'email1' exists in the eval table.
i don't want the sale.quantity to be multiplied by the number of matches in the eval table. i just want the quantity for each email if that email is also in the eval table. so if the following were the data:
create table playground.sale (email varchar(255), quantityint);
insert into sale values ('email1', 1);
insert into sale values ('email1', 2);
insert into sale values ('email1', 3);
insert into sale values ('email4', 4);
create table eval (server_id varchar(255), email varchar(255));
insert into eval values ('ServerID1', 'email1');
insert into eval values ('ServerID2', 'email1');
insert into eval values ('ServerID3', 'email1');
insert into eval values ('ServerID6', 'email1');
i would want the sum(s.quantity) for 'email1' to be 6. not 24.
April 9, 2013 at 12:35 pm
Based on the following:
create table playground.sale (email varchar(255), quantity int);
insert into sale values ('email1', 1);
insert into sale values ('email2', 2);
insert into sale values ('email3', 3);
insert into sale values ('email4', 4);
create table eval (server_id varchar(255), email varchar(255));
insert into eval values ('ServerID1', 'email1');
insert into eval values ('ServerID2', 'email1');
insert into eval values ('ServerID3', 'email2');
insert into eval values ('ServerID6', 'email3');
I guess, what I am trying to figure out is what is it you are actually asking in your query?
If you join the two tables above on email you will get a total of 4 rows, 2 with email1, 1 each for emai2 and email3.
April 9, 2013 at 1:10 pm
Sounds to me like you are trying to use SUM when what you really want is a different aggregate. Maybe MIN, MAX or even simply "top 1 order by something" is more appropriate for what you want.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 9, 2013 at 1:33 pm
Why the join to eval?
Do the following in an empty (sandbox) database:
create table dbo.sale (email varchar(255), quantity int);
insert into dbo.sale values ('email1', 1);
insert into dbo.sale values ('email2', 2);
insert into dbo.sale values ('email3', 3);
insert into dbo.sale values ('email4', 4);
create table dbo.eval (server_id varchar(255), email varchar(255));
insert into eval values ('ServerID1', 'email1');
insert into eval values ('ServerID2', 'email1');
insert into eval values ('ServerID3', 'email1');
insert into eval values ('ServerID6', 'email1');
select sum(s.quantity)
from dbo.sale s
inner join dbo.eval e
on s.email = e.email
where s.email = 'email1'
select
sum(s.quantity)
from
dbo.sale s
where
s.email = 'email1'
go
drop table dbo.sale;
drop table dbo.eval;
go
create table dbo.sale (email varchar(255), quantity int);
insert into dbo.sale values ('email1', 1);
insert into dbo.sale values ('email1', 2);
insert into dbo.sale values ('email1', 3);
insert into dbo.sale values ('email4', 4);
create table dbo.eval (server_id varchar(255), email varchar(255));
insert into dbo.eval values ('ServerID1', 'email1');
insert into dbo.eval values ('ServerID2', 'email1');
insert into dbo.eval values ('ServerID3', 'email1');
insert into dbo.eval values ('ServerID6', 'email1');
select sum(s.quantity)
from dbo.sale s
inner join dbo.eval e
on s.email = e.email
where s.email = 'email1'
select
sum(s.quantity)
from
dbo.sale s
where
s.email = 'email1'
go
drop table dbo.sale;
drop table dbo.eval;
go
April 9, 2013 at 1:37 pm
See you are getting 24 because of cartesian.
So if you use
select sum(distinct(s.quantity))--,s.email
from sale s
inner join eval e
on s.email = e.email
where s.email = 'email1'
if will give you correct result.
April 9, 2013 at 1:41 pm
dwivedi.neeraj (4/9/2013)
See you are getting 24 because of cartesian.So if you use
select sum(distinct(s.quantity))--,s.email
from sale s
inner join eval e
on s.email = e.email
where s.email = 'email1'
if will give you correct result.
Seems that MIN or MAX would produce the same results but it is far less confusing. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 9, 2013 at 1:45 pm
I may be barking up the wrong tree with this, but are you after
SELECT SUM(quantity)
FROM sale s
WHERE EXISTS (SELECT 1 FROM eval e WHERE s.email = e.email)
AND s.email = 'email1'
April 9, 2013 at 1:45 pm
No, min or max would produce a different result that what he is expecting. Min will produce 1 and max will produce 3.
April 9, 2013 at 1:49 pm
mickyT (4/9/2013)
I may be barking up the wrong tree with this, but are you after
SELECT SUM(quantity)
FROM sale s
WHERE EXISTS (SELECT 1 FROM eval e WHERE s.email = e.email)
AND s.email = 'email1'
That is the tree I was looking at, which is why I asked the question about the join to eval.
April 9, 2013 at 2:25 pm
dwivedi.neeraj (4/9/2013)
No, min or max would produce a different result that what he is expecting. Min will produce 1 and max will produce 3.
I realize that part of this is all speculation on what the OP actually wants BUT your query, MAX and MIN all three produce the same thing.
select sum(distinct(s.quantity))--,s.email
from sale s
inner join eval e
on s.email = e.email
where s.email = 'email1'
select min(s.quantity)--,s.email
from sale s
inner join eval e
on s.email = e.email
where s.email = 'email1'
select max(s.quantity)--,s.email
from sale s
inner join eval e
on s.email = e.email
where s.email = 'email1'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 9, 2013 at 2:37 pm
May be we both are working on different data set.
Here is mine.
create table playground.sale (email varchar(255), quantityint);
insert into sale values ('email1', 1);
insert into sale values ('email1', 2);
insert into sale values ('email1', 3);
insert into sale values ('email4', 4);
create table eval (server_id varchar(255), email varchar(255));
insert into eval values ('ServerID1', 'email1');
insert into eval values ('ServerID2', 'email1');
insert into eval values ('ServerID3', 'email1');
insert into eval values ('ServerID6', 'email1');
April 9, 2013 at 2:41 pm
And the OP has left without proviing any more information or feedback.
April 9, 2013 at 2:50 pm
dwivedi.neeraj (4/9/2013)
May be we both are working on different data set.Here is mine.
create table playground.sale (email varchar(255), quantityint);
insert into sale values ('email1', 1);
insert into sale values ('email1', 2);
insert into sale values ('email1', 3);
insert into sale values ('email4', 4);
create table eval (server_id varchar(255), email varchar(255));
insert into eval values ('ServerID1', 'email1');
insert into eval values ('ServerID2', 'email1');
insert into eval values ('ServerID3', 'email1');
insert into eval values ('ServerID6', 'email1');
Nope same dataset. All three of the queries I posted will return the same values. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply