Add values together

[lordfa9]

Ok lets say i have this tabeINVENTORY

Stock				Location			   QuantityFish					 A						  2Fish					 B						  3Fish					 C						  4

How do i write an SQL statement to find out what is the total amount of fish i have in all locations?

[aspnetguy]

SELECT SUM(Quantity) AS 'Number of Fish'FROM INVENTORY

that should do the trick

[jesh]

I would only add:

SELECT SUM(Quantity) AS 'Number of Fish'FROM INVENTORY WHERE Stock = 'Fish'

[aspnetguy]

I would only add:

SELECT SUM(Quantity) AS 'Number of Fish'FROM INVENTORY WHERE Stock = 'Fish'

yes, good catch

[jesh]

yes, good catch

Thanks! Obviously, in the table provided it didn't matter since all records had "Fish" in the Stock column, but going forward, I would assume more Stock items would be added.

[lordfa9]

SELECT SUM(Quantity) AS 'Number of Fish'FROM INVENTORY WHERE Stock = 'Fish'

Thanks for that answer!If the fish is spread over 3 locations, for example there are 3 tables, one for each location, is there any way i can link then all using an inner join? for example:

SELECT SUM(Quantity) AS 'Number of Fish'FROM LOCA INNER JOIN LOCB ON LOCA.StockID=LOCB.StockID WHERE Stock = 'Fish'

LOCA and LOCB are the tables for the two diferent locationsi need to find out how much fish i have in all locations

[justsomeguy]

This may work:

SELECT (SUM(LOCA.Quantity) + SUM(LOCB.Quantity)) AS 'Number of Fish'FROM LOCA, LOCB WHERE LOCA.StockID=LOCB.StockID AND LOCA.Stock = 'Fish' AND LOCB.Stock = 'Fish'

[lordfa9]

This may work:

SELECT (SUM(LOCA.Quantity) + SUM(LOCB.Quantity)) AS 'Number of Fish'FROM LOCA, LOCB WHERE LOCA.StockID=LOCB.StockID AND LOCA.Stock = 'Fish' AND LOCB.Stock = 'Fish'

I tried it and it didn't workI also noticed that you didn't use an inner join command, my database does not have any relationships so i think thats why it can't work.I was thinking, if i could use a sub query instead? For example to determine the quantity of fish that i have in LOCA, then submitting it where it will add that value to the number of fish in LOCB and give me a sum

[beagle]

Is it cheating to do it in two stages something like this?:

 create view RegionTotalFishStock as select sum(Quantity) as subcount from LOCA where stock='fish' union all select sum(Quantity) as subcount from LOCB where stock='fish'

then

 select sum(subcount)as 'Number of Fish' from RegionTotalFishStock

(I'm relatively new to this, so use with caution...)

[aspnetguy]

No that is not cheating. that is what views are for. I generally use views if I am going to be needing that data over and over.

Is it cheating to do it in two stages something like this?:

 create view RegionTotalFishStock as select sum(Quantity) as subcount from LOCA where stock='fish' union all select sum(Quantity) as subcount from LOCB where stock='fish'

then

 select sum(subcount)as 'Number of Fish' from RegionTotalFishStock

(I'm relatively new to this, so use with caution...)

[beagle]

No that is not cheating. that is what views are for. I generally use views if I am going to be needing that data over and over.

Thanks. It just feels annoying not being able to do it in one query.