Help with aggregate SQL

Lets see if I can describe what I need correctly -  Say I have a set of records that have a days and amount columns, along with PK.  Say I'm matching on days = 17 and amount = 175.     However, this is an aggregate match.  say there are 3 records like:

PK= 3, Days = 10, amount 100,
PK= 12, Days = 5, amount 50,
PK= 44, Days = 2, amount 25,

What I want returned in a query are the PK of 3, 12, and 44.
LVL 1
HLRosenbergerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, I can't understand your question.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>However, this is an aggregate match
Use the HAVING clause to perform WHERE-like expressions on an aggregate.

BUT

How can we tell that 3, 12, and 44 a related and should be group together?
0
HLRosenbergerAuthor Commented:
I want a query to return the PK, of of 3, 12, and 44, using a query like (and I know this does not work, but just trying to give an example):

SELECT PK FROM table WHERE  SUM (days) = 17 and SUM(amount) = 175.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

HLRosenbergerAuthor Commented:
Jim - there is a ClientID column as well.  That's what logically ties records together.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
So, say that the table has the following records:
PK= 1, Days = 1, amount 50,
PK= 2, Days = 4, amount 50,
PK= 3, Days = 10, amount 100,
PK= 12, Days = 5, amount 50,
PK= 24, Days = 7, amount 75,
PK= 44, Days = 2, amount 25


How do you know that should be returned (3, 12, 44) and not (3, 24), since it also matchs the criteria SUM (days) = 17 and SUM(amount) = 175.
0
HLRosenbergerAuthor Commented:
there's a client ID and date range as well.  I left that out of the original question for simplicity.

For any client and  date range, at most only one set of records will match the aggregate values.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
there's a client ID and date range as well.  I left that out of the original question for simplicity.
Isn't simplicity if you don't show us all information, specially if they are relevant.
As you can see, questions was raised immediatly because of the lack of information.
0
HLRosenbergerAuthor Commented:
ok, the records contains PK, client ID, date, days and amount, and about 30 other fields that are Irrelevant to this question.  I need in effect:

SELECT ID from table WHERE clientID=123, date='8/1/2014', SUM(days)=17, SUM(amount)=175.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. So, ID isn't the same as ClientID, right?
And one more question. If the records available for clientID=123 AND date='8/1/2014' returns the following:
PK= 3, Days = 10, amount 100,
PK= 12, Days = 5, amount 50,
PK= 44, Days = 2, amount 25,
PK= 50, Days = 1, amount 10

Should you want only (3,12,44) or in this case that exceed the SUM(days)=17 AND SUM(amount)=175 it should return nothing?
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
given the above definition, and assuming date means date and not datetime...
SELECT yt.ID 
FROM YourTable yt
   JOIN (
      SELECT ClientId, [Date], SUM(days) as days_sum, SUM(amount) as amount_sum
      FROM YourTable
      GROUP BY ClientId, [Date]
      HAVING SUM(days) = 17 AND SUM(amount) = 175) yt_sum 
         ON yt.ClientId = yt_sum.ClientId AND yt.date = yt_sum.date

Open in new window

As Vitor posted there are a couple of scenarios where this could get ugly real quick
0
HLRosenbergerAuthor Commented:
There wont be a record like:

PK= 50, Days = 1, amount 10.

The aggregate value for days and amount that I will be using in the SQL with either match exactly with the set of records, or will not match.
0
HLRosenbergerAuthor Commented:
Jim -  don't want to join based on clientID.   I will be picking off the records per client, so I want to have  WHERE clause like WHERE clientID = '1234'
0
HLRosenbergerAuthor Commented:
I used a UNION to accomplish this:

SELECT ID, days, service_total
  FROM [tds].[dbo].[tncp_ioctrx]
  where  county_id = '252447H'  and per_diem = 37.68 and transaction_type = 'ar'

  union

  SELECT -1, sum(days), sum(service_total)
  FROM [tds].[dbo].[tncp_ioctrx]
  where  county_id = '252447H'  and per_diem = 37.68 and transaction_type = 'ar'
   
  order by id
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
HLRosenberger, there's nothing in your query that says to return only records that meet the criteria SUM(days) = 17 AND SUM(amount) = 175
0
HLRosenbergerAuthor Commented:
This worked for me.,
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.