sum of rows

  • Thread starter Thread starter cdolphin88
  • Start date Start date
C

cdolphin88

SELECT SUM(Binding_physical_damage + binding_damaging_repairs +
binding_chemical_damage + binding_biological_damage +
binding_brittle_paper) as RowSumBinding
FROM TB_TYPES_OF_DAMAGE;

I have this query and it give the sum of the rows, but I need a query
which gives me a different result...
I want the result to be 5 (5 instances of 2)


Binding_physical_damage 2
binding_damaging_repairs 2
binding_chemical_damage 2
binding_biological_damage 2
binding_brittle_paper 2
RowSumBindging 5

Is that possible to make a query like this?


Cheers!

C
 
Use your first query as the source for a second query. If you

SELECT RowSumBinding, Count(RowSumBinding) as CountNumber
FROM YourOriginalQuery
GROUP BY RowSumBinding.

You could try to do this all in one query using a subquery (which is your
original query string).


SELECT RowSumBinding, Count(RowSumBinding) as CountNumber
FROM (
SELECT SUM(Binding_physical_damage + binding_damaging_repairs +
binding_chemical_damage + binding_biological_damage +
binding_brittle_paper) as RowSumBinding
FROM TB_TYPES_OF_DAMAGE
) as QSource
GROUP BY RowSumBinding.
 
Hi John,

It doesn't work. it shows me like this:

rowsumbinding countnumber
10 1


I want the query to show me that I have 5 instances of 2 instead of 10


Cheers!


C

John Spencer escreveu:
 
Sorry, I misunderstood your original posting. I thought the results you
showed were from the original query. Now I see that your original query
returned one record with the value = 10.

You have a design problem in that your damage fields should actually be in a
table where you have fields for the type of damage and the amount(?) of
damage and the book identifier or what ever is being damaged.

You can use a UNION query to normalize the data and then use that as the
source of your summary queries

SELECT Binding_physical_damage as Damage, "Physical" as DamageType
FROM TB_TYPES_OF_DAMAGE
UNION ALL
SELECT binding_damaging_repairs , "Repairs"
FROM TB_TYPES_OF_DAMAGE
UNION ALL
SELECT binding_chemical_damage , "Chemical"
FROM TB_TYPES_OF_DAMAGE
UNION ALL
SELECT binding_biological_damage, "Biological"
FROM TB_TYPES_OF_DAMAGE
UNION ALL
SELECT binding_brittle_paper, "Paper"
FROM TB_TYPES_OF_DAMAGE

Save that as QAllDamage and use it as your source query
SELECT Damage, Count(Damage) as CountValues
FROM QAllDamage
GROUP BY Damage

Limit that to only those with 2 as the count
SELECT Damage, Count(Damage) as CountValues
FROM QAllDamage
WHERE Damage = 2
GROUP BY Damage

If you wanted to get damage by type and number then
SELECT DamageType, Damage, Count(Damage) as CountValues
FROM QAllDamage
GROUP BY DamageType, Damage

If you wanted the total of Damage by Types
SELECT DamageType, Sum(Damage) as HowMuchDamage
FROM QAllDamage
GROUP BY DamageType
 
Back
Top