getting the correct sum when join condition works.

  • At least not for me.

    Its giving 6, 1 & 3 respectively.

  • dwivedi.neeraj (4/9/2013)


    At least not for me.

    Its giving 6, 1 & 3 respectively.

    Ahh it is a different dataset. I used the original one from the OP. 😛

    _______________________________________________________________

    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/

  • and I used second one.. which was more specific.

  • my apologies, guys. i was pulled into an urgent issue. that is not going well. i think in trying to simplify my problem to present here, i've made it more complicated. i don't really need the where clause, that was just to narrow it down. basically, i just want the total quantity for all rows in table 'Sale' for which there is a match on 'email' in table Eval. i know i can get what i want this way:

    select sum(quantity) from sale where email in (select email from eval)

    however, that's taking forever and i was hoping that i could make it faster if i could use a join instead of a subquery.

    thanks again for the responses.

  • bagofbirds-767347 (4/10/2013)


    my apologies, guys. i was pulled into an urgent issue. that is not going well. i think in trying to simplify my problem to present here, i've made it more complicated. i don't really need the where clause, that was just to narrow it down. basically, i just want the total quantity for all rows in table 'Sale' for which there is a match on 'email' in table Eval. i know i can get what i want this way:

    select sum(quantity) from sale where email in (select email from eval)

    however, that's taking forever and i was hoping that i could make it faster if i could use a join instead of a subquery.

    thanks again for the responses.

    You may want to try this:

    select sum(quantity) from sale s where exists(select 1 from eval e where e.email = s.email)

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply