[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3706
  • Last Modified:

SQL SumIf Question

Hi:

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.

DATA:

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.
0
MercyHousing
Asked:
MercyHousing
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
select property, sum(amount)
from yourtable
where segment in ('001', '002')
group by property
0
 
tim_csCommented:
SELECT
   Property
   ,SUM(Amount)
FROM
   YourTable
WHERE
   Segment = '001' OR Segment = '002'
GROUP BY
   Property
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT PROPERTY, SUM (amount)
FROM table1 t1
INNER JOIN table2 t2 ON t1.segment = t2.segment
GROUP BY PROPERTY
0
 
MercyHousingAuthor Commented:
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.
0
 
Éric MoreauSenior .Net ConsultantCommented:
select property, sum(amount)
from yourtable
inner join segmenttable
on segmenttable.id = yourtable.segmentID
and segmenttable.segment in ('001', '002')
group by property
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now