SQL SumIf Question

Posted on 2012-09-13
Last Modified: 2012-09-13

I would like to group and total some data using a third column as the reference but I don't want that in the output (not in the group by) and am having problems.  Here is an oversimplified view of what I need.  I would like to total the amount column where the third column is either a value of 001 or 002.


property                     amount            segment

A            5.00            001
B            2.00            001
A            1.00            002
A            3.00            001
B            4.00            003
C            1.00            004

Outcome (only those properties that have a segment of 001 or 002)

A            9.00            
B            2.00

I tried something like select sum(case when segment in (select ssegment from table2.....) then amount end)  group by property, amount but it says I need the segment in the group by and if I do that then I get totals for both 001 and 002.  

I hope this makes sense.

Thank you for any suggestions you may have.
Question by:MercyHousing
    LVL 69

    Expert Comment

    by:Éric Moreau
    select property, sum(amount)
    from yourtable
    where segment in ('001', '002')
    group by property
    LVL 15

    Expert Comment

       Segment = '001' OR Segment = '002'
    LVL 75

    Accepted Solution

    FROM table1 t1
    INNER JOIN table2 t2 ON t1.segment = t2.segment

    Author Comment

    Thank you for the response but I was oversimplifying things.  There are about 200 of the segment values and I am getting those by a second select from table2.   This part of the query is like this

    sum(case when segment in (select values from table2.....) then amount end)  

    group by property

    The error I get is like this

    Column 'segment' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.  

    If I add it in in the GROUP BY then I get totals for both 001 and 002.
    LVL 69

    Expert Comment

    by:Éric Moreau
    select property, sum(amount)
    from yourtable
    inner join segmenttable
    on = yourtable.segmentID
    and segmenttable.segment in ('001', '002')
    group by property

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how the fundamental information of how to create a table.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now