CODEWITHSUNDEEP
sqlsql-server
Ammar Ismaeel
Ammar Ismaeel

Reputation: 803

How to write a query that get the sum of quantity of multiple rows from multiple tables?

I want to write a query that bring me the sales for today. I am using 4 tables in the database.

It may be multiple rows for one drug each expiry date is different, I want to get the sum of quantity for each drug of that are sold today.

here the query I am using but its giving me wrong numbers:

select DrugID,
       Drugs.Name,
       sum(InputDetails.Quantity) as 'Quantity' 
from InputDetails 
inner join Drugs on Drugs.ID = InputDetails.DrugID 
cross join Output
inner join OutputDetails on OutputDetails.OutputID = Output.ID
group by DrugID, Name;

Upvotes: 2

Views: 708

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35563

Sales in a day is derived from tables 2 and 3 in your list

Output it contains the sale id and the date

outputdetails contains the drugs that sold in specific sale

But you have not identified sufficient columns in those tables to enable a full query to be built.

Select ...
From Output
inner join OutputDetails on OutputDetails.OutputID = Output.ID
Group by ...

You could also include the first table "drugs" in the query but again we dont know enough yet.

Subsequent to your first comment below, if you do need to combine sales and stock information into a single result you need to aggregate sales and stock separately then join those to common information found in the drugs table. There isn't enough details about the tables in your question so the following is provided as a guide only. There has to be some way to explicitly join drugs to sales data (i.e. it cannot be a CROSS JOIN between drugs and output).

SELECT
      Drugs.ID
    , Drugs.Name
    , r.Stock
    , s.sales
FROM Drugs
LEFT JOIN (
      SELECT
            DrugID
          , SUM(InputDetails.Quantity) AS 'Stock'
      FROM InputDetails
      GROUP BY
            DrugID
      ) r ON Drugs.ID = r.DrugID
LEFT JOIN (
      SELECT
            output.DrugID
          , SUM(OutputDetails.Quantity) AS 'Sales'
      FROM Output
      INNER JOIN OutputDetails ON OutputDetails.OutputID = Output.ID
      GROUP BY
            output.DrugID
      ) o ON Drugs.ID = o.DrugID

Upvotes: 1

Related Questions