Subtotal/allocation by unique fields (1 Viewer)

vfxd · Nov 13, 2012

I have a table with the following sample data (the data and field names are fictionalized... I'm not trying to track pieces of fruit!):

Code:

FruitID  Owner   Location  Type
-------  ------  --------  ----
1        John    New York  Apple
2        John    New York  Apple
3        John    New York  Orange
4        John    New York  Banana
5        John    New York  Banana
6        John    Chicago   Apple
7        Mary    Miami     Banana
8        Mary    Miami     Banana
9        Mary    Miami     Banana
10       Mary    Atlanta   Banana

I want to create a query which will generate the following:

Code:

Owner  Type    Location  Count  Allocation
-----  ----    --------  -----  ----------
John   Apple   New York  2      0.667
John   Apple   Chicago   1      0.333
John   Banana  New York  2      1.000
John   Orange  New York  1      1.000
Mary   Banana  Atlanta   1      0.250
Mary   Banana  Miami     3      0.750

Count is the count of FruitID for each unique Owner/Type/Location. Allocation is calculated as the Count divided by the total count for each unique Owner/Type entry. The trick here is that the Allocation is not calculated across each Owner (where the Allocation for John/Apple/Chicago would equal 1/6, because there are 6 records for John), but across each unique Owner/Type pair (so John/Apple/Chicago is calculated as 1/3 = 0.333, because there are 3 records for John/Apple).

I can do it without the Allocation field:
SELECT Owner, Type, Location, Count(FruitID) FROM MyTable GROUP BY Owner, Type, Location;

But I can't figure out how to add the Allocation field/calculation. Is there a single SQL statement that can get me the second table/recordset above? Thanks in advance!

sxschech · Nov 13, 2012

This may get you closer. Add another query to count the Type by owner and join it to your original query, then you can do the calculation.

Code:

SELECT A.Owner, A.Type, A.Location, CountOfFruitID, CountOfFruitID/CountOfType as Allocation FROM
(
SELECT Fruit.Owner, Fruit.Type, Fruit.Location, Count(Fruit.FruitID) AS CountOfFruitID
FROM Fruit
GROUP BY Fruit.Owner, Fruit.Type, Fruit.Location) A
INNER JOIN
(
SELECT Fruit.Owner, Fruit.Type, Count(Fruit.Type) AS CountOfType
FROM Fruit
GROUP BY Fruit.Owner, Fruit.Type
) B
ON A.Owner = B.Owner
and A.Type = B.Type

AccessMSSQL · Nov 13, 2012

You need to do something like this:

Code:

SELECT Owner, Location, Type, Count(FruitID) as CountOfFruitID, Count([FruitID) / DLookup("CountOfOwner","qryOwnerCounts","Owner='" & [Owner] & "'") as Allocation
FROM YourTable
GROUP BY Owner, Location, Type

Then create a separate query called qryOwnerCounts that looks like this:

Code:

select Owner, Count([Owner]) as CountOfOwner from YourTable
Group By Owner

vfxd · Nov 13, 2012

Thanks for the suggestions! I think I got it using sxschech's suggestion. Not sure if I anonymized for the fake fruit data correctly, but here is what I'm using:

Code:

SELECT a.Owner, a.Location, a.Type, Count(a.FruitID) As CountForOwnerLocType, CountForOwnerLocType / b.CountForOwnerType
FROM MyTable As a
LEFT JOIN
    (
    SELECT Owner, Type, Count(FruitID) As CountForOwnerType
    FROM MyTable
    GROUP BY Owner, Type
    ) As b
ON a.Owner=b.Owner AND a.Type=b.Type
GROUP BY a.Owner, a.Location, a.Type, b.CountForOwnerType
ORDER BY a.Owner, a.Type, a.Location;

Subtotal/allocation by unique fields (1 Viewer)

vfxd

New member

sxschech

Registered User.

AccessMSSQL

Seasoned Programmer-ette

vfxd

New member

Similar threads

Users who are viewing this thread