Sum values across columns with 1 of 2 keys the same 1

[Cero21]

I have a table with 2 primary keys: Unit and Type

Each record has a field called allocation(number)

What I would like to do is find the total "allocation" for each type in a query. Another way of putting this is I would like to know my total number of items from all the units combined broken down by type.

Any idea how to do this? Thanks

 

[PHV]

Something like this ?
SELECT Type, Sum(allocation) As TotalAllocation, Count(*) As CoutOfItems
FROM yourTable
GROUP BY Type

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

 

[Cero21]

Exactly what I was looking for. Thanks!

 

[Cero21]

Well, I guess I found another question related to this. How do I sum these results together?

Here what I got. As well as allocation I have fields for every month and would like to have quarterly sums. So now I can find the sum for each month but how do I add 3 months (results) together?

 

[PHV]

How do you sum for each month ?
If you have a date field, you may group by Format([date field],'q')

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

 

[Cero21]

Each month is a number type so the user just types in how much was used for each month.
ie. JAN - 10 Feb - 20 etc

 

[PHV]

So, again, How do you sum for each month ?

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

 

[Cero21]

I'm using the same thing you showed me above for "allocation".

 

[Cero21]

Any ideas?

 

[lespaul]

could you provide sample data and expected results from the data?



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

 

[Cero21]

Basically here is what I have.

Equipment(Type(key),Unit (key), allocation, Jan, Feb, Mar, Apr..., Dec)

Type is the type of equipment it is (ie glowsticks).
Unit is the unit number which holds the equipment (we have 4).
Allocation is the number of each equipment they initially started with.
The month fields (Jan, Feb etc) are numbers which represent the amount used.

Therefore a typical record would look like this.
Glowstick, 741(Unit number), 100(allocation), 3(jan), 23(Feb), 45 (Mar).. etc

What I would like to do is create quarterly statistics that encompass all the units.

So Jan - Mar would be one quarter, Apr - Jun another etc

What I would like to see can be best described in an example (for simplicity only account for 1 quarter but I would like all 4).

Taking 3 Units:
Glowstick, 741, 40, 10(jan), 11(feb), 12(mar)
Glowstick, 742, 24, 4(jan), 6(feb), 10(mar)
Glowstick, 743, 35, 10(jan), 6(feb), 3(mar)

I would like these stats calculated:
1st Quarter: (10+11+12+4+6+10+10+6+3) = 70
% Used: 70 / (40+24+35) = .707

I don't know what I would write in a query to generate this.

Thanks.

 

[PHV]

A starting point:
SELECT Type, Sum(allocation) As Total, Count(*) As NumberOfItems
, Sum(Nz(Jan,0)+Nz(Feb,0)+Nz(Mar,0)) As [1st Quarter], [1st Quarter]/Total As [% Used Q1]
, ...
, Sum(Nz(Oct,0)+Nz(Nov,0)+Nz(Dec,0)) As [4th Quarter], [4th Quarter]/Total As [% Used Q4]
FROM Equipment
GROUP BY Type

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

 

[Cero21]

Worked! Thanks.