We help IT Professionals succeed at work.

SUM(Total of Dollars) by Matter Number WHERE Dolalrs >=?

I am trying to perform the following – We have a table that has all time entries

MatterNumber, Dollars, Dateworked etc… this table has ALL time entries that were ever recorded.


MatNumber      Dollars            Date
101            500            12/7/2011
102            25            12/8/2011
101            700            12/9/2011
102            25            12/8/2011
101            800            12/7/2011
102            25            12/3/2011
101            100            12/4/2011
102            25            12/5/2011

What I would like to do is run a report that will GROUP the Dollars by Matter Number and only return results that have a SUM(Dollars by Matter Number) that is over lets say 500 dollars..

So the results would show
MatNumber      Total Dollars
101            2100

And not bring 102 since the total is under the 500 mark…  

This is what I have so far but this is not calculating the TOTAL of the Matter ?




select Matnum,DateWorked,sum(Dollars) from tabtime
INNER JOIN
		tabMatters ON tabMatters.MatKey = tabTime.MatKey
where WipStatus ='Wip'
 and Dollars>=200000
Group By Matnum,Dateworked

Open in new window

Comment
Watch Question

What you are looking for is the SQL HAVING clause
http://www.w3schools.com/sql/sql_having.asp
Are you looking for this querry

select Matnum,DateWorked,sum(Dollars)
from tabtime INNER JOIN
            tabMatters ON tabMatters.MatKey = tabTime.MatKey
where WipStatus ='Wip'
 and sum(Dollars) >=200000
Group By Matnum,Dateworked
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
select Matnum,DateWorked,sum(Dollars) from tabtime
   INNER JOIN  tabMatters ON tabMatters.MatKey = tabTime.MatKey
where WipStatus ='Wip'
Group By Matnum,Dateworked
HAVING SUm(Dollars) > 200000    -- Any WHERE criteria that involves an aggregate (sum, count, etc.) goes in the HAVING clause here
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Thanks for the split.  Good luck with your project.  -Jim