Help using summed field in where 2

[moonbase]

I am sure this is easy but I can't get my head round it.

I have 2 tables in a 1 to many relationship.
Table 1 (one) contains
itemid
qtyordered
Table 2 (many) contains
itemid
qtyreceived

What I want is
itemid
qtyordered
sum(qtyreceived)
but only where qtyordered > qtyreceived

The problem seems to be how to reference the summed field in the where clause.

 

[lespaul]

to use a SUM (or any other aggregate function) to filter you need to use a HAVING clause.

SELECT a.itemid, a.qtyordered, SUM(b.qtyreceived) FROM Table1 A INNER JOIN Table2 B on A.itemID = B.itemID HAVING a.qtyordered > SUM(b.qtyreceived)



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

SELECT A.itemid, A.qtyordered, Sum(B.qtyreceived) AS received
FROM [Table 1] AS A INNER JOIN [Table 2] AS B ON A.itemid = B.itemid
GROUP BY A.itemid, A.qtyordered
[!]HAVING A.qtyordered > Sum(B.qtyreceived)[/!]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Melagan]

SQL should be something like this:

SELECT t1.ItemID
, t1.qtyordered
, sum(t2.qtyreceived) as SumOfReceived

FROM [Table 1] as t1 INNER JOIN [Table 2] as t2 ON t1.ItemID = t2.ItemID

WHERE t1.qtyordered > t2.qtyreceived;

If that doesn't work, I'd like to see the SQL you're working with along with some sample data and expected results of the query =)

~Melagan
______
"It's never too late to become what you might have been.

 

[Melagan]

Wow I was too late and totally skipped over the HAVING clause... just delete my post =)

~Melagan
______
"It's never too late to become what you might have been.

 

[moonbase]

Wow that was quick, thanks. Sorry, one thing I omitted was that I also need qtyordered - sum(qtyreceived). This is where it's tricky because I can't just include this, or can I?

 

[lespaul]

sure you can, this should work:

SELECT a.itemid, a.qtyordered, SUM(b.qtyreceived), a.qtyOrdered - SUM(b.qtyReceived) as Shortage FROM Table1 A INNER JOIN Table2 B on A.itemID = B.itemID
GROUP BY a.itemid, a.qtyordered, a.qtyOrdered - SUM(b.qtyReceived)
HAVING a.qtyordered > SUM(b.qtyreceived)

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

Leslie, your GROUP BY clause seems suspect to me ...
SELECT A.itemid, A.qtyordered, Sum(B.qtyreceived) AS received, A.qtyordered - Sum(B.qtyreceived) AS remainder
FROM [Table 1] AS A INNER JOIN [Table 2] AS B ON A.itemid = B.itemid
GROUP BY A.itemid, A.qtyordered
HAVING A.qtyordered > Sum(B.qtyreceived)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

yeah, I wasn't really sure if the subtraction portion of the select needed to be there....
les

 

[PHV]

The rules:
any non aggregated non constant column appearing in the SELECT list should be GROUPed.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

I guess my confusion was coming from the fact that the single value [a.qtyordered - SUM(b.qtyrecieved)] had both an aggregate and a non-aggregate. Hopefully I won't be confused next time!

thanks for the pointer,
les

 

[moonbase]

Thanks guys, that's nailed it.

As a matter of interest I pasted the sql into an Access query and had a look at the corresponding design view to see where I gone wrong. The main thing seems to be to use "expression" on the total line and "sum" within the field.