By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,041 Members | 1,734 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,041 IT Pros & Developers. It's quick & easy.

Combining Aggregate functions in Access (SQL)

P: 7

I have a question that has been dogging me for awhile now, and I would like some fresh insight :

Given a table of products (product_id), each with a specific inventory (inv_count), and each provided by a certain vendor (vendor_id) in a 'Products' table as below
[HTML]product_id inv_count vendor_id
1 2 3
1 5 3
1 5 4
2 5 5
2 3 6
2 3 6[/HTML]
How could I write an SQL query that would return the Maximum total inventory for a given product by a vendor? So the result would look like :

[HTML]product_id total_inv vendor_id
1 7 3
2 6 6[/HTML]
I realize this would be a horrible design, I am just using it as an example. I have been working with SQL for awhile, and these types of queries always kill me! Any help or advice is greatly appreciated...
Dec 13 '07 #1
Share this Question
Share on Google+
9 Replies

Expert Mod 10K+
P: 12,366
You seem to have forgotten 1-5-4 and 2-5-4.

Just do a sum aggregate and group by the other two fields.
Dec 14 '07 #2

Expert Mod 15k+
P: 31,494
You want to group similar [product_id] & [vendor_id] values together but sum the [inv_count] within that.
Expand|Select|Wrap|Line Numbers
  1. SELECT [product_id], [vendor_id] Sum([inv_count]) AS SumInv
  2. FROM [Products]
  3. GROUP BY [product_id], [vendor_id]
Dec 14 '07 #3

P: 7
Thank you both (Rabbit and NeoPa) for the replies. However, I don't think I stated the question clearly enough, as I had already done what you suggested, but didn't get the result I am looking for.

I am trying to retrieve the total inventory, product id, and vendor id for the single product with the Maximum total inventory.

For example : for product 1, there are 7 total for vendor number 3, and 5 total for vendor number 4. So, in the result set, I just want 1, 7, 3 returned; and the same for product 2.

Again, this is not the best example, and would be a horrible database design - I'm just focusing on the concepts. Again, any advice is greatly appreciated.
Dec 14 '07 #4

Expert Mod 10K+
P: 12,366
If you need vendor_id then use a subquery on a query that selects distinct product_ids. Otherwise you can just run a max aggregate query on the sum aggregate query.
Dec 14 '07 #5

Expert Mod 15k+
P: 31,494
So, if I understand you correctly now, you want to :
Show the [product_id], [vendor_id] and the total (within these) of [inv_count], but only for the [vendor_id] within each [product_id] which has the largest total [inv_count].
That would be one line per [product_id].

In that case you were almost there before. You simply need to take the previous query as the source of a new GROUP BY query and GROUP BY the [product_id] (fiddling with the [vendor_id] a bit too of course to select the one that matches the [MaxInv]).
Expand|Select|Wrap|Line Numbers
  1. SELECT [product_id],
  2.        Val(Mid(Max(Format([SumInv],'000000000') &
  3.                    [vendor_id]),10)) AS MaxVendor,
  4.        Max([SumInv]) AS MaxInv
  5. FROM (SELECT [product_id],
  6.              [vendor_id],
  7.              Sum([inv_count]) AS SumInv
  8.       FROM [Products]
  9.       GROUP BY [product_id], [vendor_id]) AS subQ
  10. GROUP BY [product_id]
Dec 14 '07 #6

P: 7
Wow - that's awesome! Thank you.

I understand the general concept, but would you mind explaining the whole "fiddling with the vendor_id" part? That seems to be the part I am getting hung up on and don't quite understand. This line in particular :
Expand|Select|Wrap|Line Numbers
  1. Val(Mid(Max(Format([SumInv],'000000000') &
  2.                    [vendor_id]),10)) AS MaxVendor,
  3.        Max([SumInv]) AS MaxInv
Once again - thanks so much for your time.
Dec 14 '07 #7

Expert Mod 15k+
P: 31,494
In truth, I only realised you were asking for the ASSOCIATED Vendor after I'd done most of the answer and was running a little short on time to explain properly. The "fiddling" isn't actually as straightforward as I made it sound I suppose.

The technique is to find the Vendor that is associated with the largest inventory count. The inventory count total per vendor is already available to us as [SumInv]. What we do then is create a field in our SQL which is a concatenation of both this figure AND the related (associated) Vendor. It's sometimes possible to do this numerically, but it's generally possible with a string result, and is more easily manipulated that way. We use a string.
Expand|Select|Wrap|Line Numbers
  1. Format([SumInv],'000000000') & [vendor_id]
We must format the [SumInv] figure as we will later be extracting part of the result AFTER this value. To do that we must know how many characters to skip. If we set it ourselves with Format() then that becomes easy. Next we get the Max() of this. Because the [SumInv] is the first item, the Max() will return the string for the record with the largest value of [SumInv]. Tagged along with that though, will be the [vendor_id], starting in position ten. That moves us on to the next step, which is Mid(..., 10) which strips the [SumInv] part of the string. We are only left with the step of converting the string holding the value we want into a numeric value ([MaxVendor]).
That's how and why it works ;)
Dec 15 '07 #8

P: 7

Thank you so much for the detailed explanation - I greatly appreciate it.This technique will open up a lot of new possibilities for me.

I hope you and yours have a very Happy Holiday Season.
Dec 17 '07 #9

Expert Mod 15k+
P: 31,494
Not a problem Kev, and thank you for the kind wishes.
Back at you of course, for a fine Christmas and a Jolly (read that how you like) New Year.
Dec 17 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.