How to subtract sale quantity from purchase quantity in one query?

Question

I have two queries:

str = "select sum(Quantity) from AddStock where ProductId=" +id.ToString()  + " AND Type IN('Purchase','StockInward','Sale Return') AND EntryMode='Godown'";
Qty = dc.Retrieve_Datareader(str);
str = "select sum(Quantity) from AddStock where ProductId=" +id.ToString() + " AND Type IN('StockOutward','Sale','Wastage','Purchase Return') AND EntryMode='Godown'";
Qty1 = dc.Retrieve_Datareader(str);

return(Qty - Qty1);

How do I write the above two queries in one query and get the difference?

Answer 1

You can query multiple tables at once.

Query:

SELECT SUM(as1.Quantity) AS SR,
       SUM(as2.Quantity) AS PR,
       SUM(as1.Quantity) - SUM(as2.Quantity) AS Difference
FROM   AddStock as1, AddStock as2
WHERE  as1.ProductId = @ProductId
AND    as2.ProductId = as1.ProductId
AND    as1.Type IN ('Purchase','StockInward','Sale Return')
AND    as2.Type IN ('StockOutward','Sale','Wastage','Purchase Return')
AND    as1.EntryMode = 'Godown'
AND    as2.EntryMode = as2.EntryMode

Code:

using(var command = new OleDbCommand(query, connection))
{
    command.Parameters.AddWithValue("@ProductId", id);

    using(var reader = command.ExecuteReader())
    {
        if(reader.Read())
        {
            var sr = Convert.ToInt32(reader[0]);
            var pr = Convert.ToInt32(reader[1]);
            var difference = Convert.ToInt32(reader[2]);
        }
    }
}