Adding the total quantity of a type of record in a table to each record 1

[irethedo]

I have a table that contains four fields: Unit, Device, Qty, and LineNo
and I would like to add a fifth field that contains the total quantity
of Devices or Units of each specific type within that table to each record
such as total field in the example below.

In other words, There are a total of 20 Units 019933 and a total of
16 Devices 008117, hence the totals in the following example:

[pre]Unit Device Qty LineNo Total
019933 4 1 20
019933 8 5 20
019933 8 7 20
019935 2 6 6
019935 4 20 6
008117 8 1 16
008117 8 19 16
015968 2 3 6
015968 4 14 6
019838 2 15 2[/pre]

Is there an easy way to add this total to each record in this fifth field?

Thanks

 

[SkipVought]

Hi,

Aggregations should not be stored in tables, rather calculated at run time.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MajP]

SELECT A.Unit, A.Device, A.Qty, A.LineNo, (Select sum(B.qty) from tblDevice as B where A.Device = B.Device or A.Unit = B.Unit) AS Total
FROM tblDevice AS A;

 

[irethedo]

Thanks MajP

This works really well

I tried using the same method on another table that I have but it reports the totals on each line for all items for the particular grouping.

My SQL looks like this;

SELECT A.PC, A.SP, A.Qty, A.MySum, A.PreUsed, A.LineNO, A.Tend_SP, A.Tend_Lic, A.MLIC, A.MPC, (Select sum(B.Qty) 
from PC_SP_Line_tbl as B where A.PC = B.PC or A.SP = B.SP) AS Total 
FROM PC_SP_Line_tbl AS A;

and the results look like this:
[pre]
PC SP Qty MySum PreUsed LineNO Tend_SP Tend_Lic MLIC MPC Total
019933 4 4 6 26
019933 8 8 5 26
019933 8 8 27 26
019935 2 2 35 26
019935 4 4 20 26
019838 4 4 8 60
ENCRYPTED 4 4 9 60
019838 4 4 24 60
ENCRYPTED 4 4 25 60
008117 8 8 27 60
008117 8 8 31 60
105733 8 8 32 LC301, LC101, DMLC 60
019838 8 8 33 60
ENCRYPTED 8 8 34 60
019838 2 2 39 60
ENCRYPTED 2 2 40 60[/pre]

Not sure why this is so different from your example as the SQL looks like it follows the same format...

 

[MajP]

Do you own the source of this table, and can you redesign it? Some of your problem is that the table is not normalized. You should have one column ItemID and then another column for itemType (PC or SP) similary (Unit or Device). That would make your queries much easier. Fixing it would be easy using a normalization union query.

My guess is that the empty SP or PC fields are not null but have an empty string. Null cannot equal null, but I am wondering if empty string will equal and empty string thus giving the whole group. If you cannot normalize the table then make a union query to put PC and SP in one column and you could still keep the separate columns. Then do the sum on the normalized query.

 

[irethedo]

Thanks MajP

I can add an auto number ItemID field to my table but the reason for an individual field for PC and SP
as there are several of each of these types.

Or are you suggesting that I lump all PC and SP items in one field and then have another field
to differentiate one from another?

In other words this:
[pre]ID Device Type Qty Line
x 019933 PC 4 1
x 008117 SP 8 1[/pre]

Instead of what I currently have:

[pre]ID PC SP Qty Line
x 019933 4 1
x 008117 8 1[/pre]

The fields are text fields and they either contain a text value or they are Null but there are
an empty string as I am testing for NULL on these fields later on in my code.

Thanks again

 

[MajP]

Or are you suggesting that I lump all PC and SP items in one field and then have another field
to differentiate one from another?

Yes. You Would have a pet table like this

[tt]PetName PetType
Fido     Dog
Molly    Cat
Rover    Dog
Snowball Cat

And not

DogName  CatName
Fido
         Molly
Rover
         Snowball[/tt]

 

[MajP]

However, this may work
A.PC = B.PC or A.SP = B.SP
to
(A.PC & A.SP) = (B.PC & B.SP)

 

[Andrzejek]

I may even go further and do this:

[pre]
PetName PetType
Fido 1
Molly 2
Rover 1
Snowball 2

Table: MyPetType
PetType PetDescr
1 Dog
2 Cat[blue]
3 Crocodile[/blue]
[/pre]
So adding [blue]a new type of pet[/blue] is easy.

Have fun.

---- Andy

There is a great need for a sarcasm font.