lordfa9 Posted November 1, 2006 Share Posted November 1, 2006 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? Link to comment Share on other sites More sharing options...
aspnetguy Posted November 1, 2006 Share Posted November 1, 2006 SELECT SUM(Quantity) AS 'Number of Fish'FROM INVENTORY that should do the trick Link to comment Share on other sites More sharing options...
jesh Posted November 1, 2006 Share Posted November 1, 2006 I would only add: SELECT SUM(Quantity) AS 'Number of Fish'FROM INVENTORY WHERE Stock = 'Fish' Link to comment Share on other sites More sharing options...
aspnetguy Posted November 1, 2006 Share Posted November 1, 2006 I would only add:SELECT SUM(Quantity) AS 'Number of Fish'FROM INVENTORY WHERE Stock = 'Fish' yes, good catch Link to comment Share on other sites More sharing options...
jesh Posted November 1, 2006 Share Posted November 1, 2006 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. Link to comment Share on other sites More sharing options...
lordfa9 Posted November 2, 2006 Author Share Posted November 2, 2006 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted November 2, 2006 Share Posted November 2, 2006 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' Link to comment Share on other sites More sharing options...
lordfa9 Posted November 2, 2006 Author Share Posted November 2, 2006 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 Link to comment Share on other sites More sharing options...
beagle Posted November 3, 2006 Share Posted November 3, 2006 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...) Link to comment Share on other sites More sharing options...
aspnetguy Posted November 3, 2006 Share Posted November 3, 2006 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...) Link to comment Share on other sites More sharing options...
beagle Posted November 3, 2006 Share Posted November 3, 2006 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now