asked on

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.

Vitor Montalvão

Sorry, I can't understand your question.

Jim Horn

>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?

HLRosenberger

ASKER

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.

HLRosenberger

ASKER

Jim - there is a ClientID column as well. That's what logically ties records together.

Vitor Montalvão

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.

HLRosenberger

ASKER

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.

Vitor Montalvão

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.

HLRosenberger

ASKER

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.

Vitor Montalvão

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?

Jim Horn

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

HLRosenberger

ASKER

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.

HLRosenberger

ASKER

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'

ASKER CERTIFIED SOLUTION

HLRosenberger

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

HLRosenberger, there's nothing in your query that says to return only records that meet the criteria SUM(days) = 17 AND SUM(amount) = 175

HLRosenberger

ASKER

This worked for me.,