MySQL Sales Records – Select Units Sold and Total Units Sold

MySQL

I am trying to Select Units Sold and Total Units Sold from a Sales record table using.

When I use this Query, I can get the Units Sold for '2016-04-14'

SELECT salesrecord.itemID, salesrecord.itemname, 
   SUM(salesrecord.itemunits) AS `Units Sold` , 
   SUM(salesrecord.itemunits)
WHERE `date`='2016-04-14' 
GROUP BY salesrecord.itemname

And When I Use this query, I can get the Total units Sold till date

SELECT salesrecord.itemID, salesrecord.itemname, 
   SUM(salesrecord.itemunits) AS `Total Units Sold` , 
   SUM(salesrecord.itemunits) 
WHERE `date`<='2016-04-14' 
GROUP BY salesrecord.itemname

What I will like to do is to get both "Units Sold" And "Total Units Sold" on a single table as shown in image attached

Best Answer

You need a conditional aggregate using CASE:

SELECT sr.itemID, sr.itemname,
   SUM(sr.itemunits) AS "Total Units Sold", 
   SUM(CASE WHEN date='2016-04-14' THEN sr.itemunits END) AS "Units Sold"
FROM salesrecord AS sr
WHERE date <= '2016-04-14'
GROUP BY sr.itemID, sr.itemname

I used a table-alias sr which improves readability.

And I added sr.itemID to GROUP BY, which is compliant with Standard SQL (and the latest version of MySQL)

Related Solutions

Mysql – One to many join, Sum from both table

Suppose your date range is 2014-12-08 to 2014-12-11

This would be the Query you need

SELECT B.*,A.quantity
FROM
(
    SELECT
        AA.id,SUM(BB.quantity) quantity
    FROM
    (
        SELECT id FROM orders
        WHERE date >= '2014-12-08' AND date <= '2014-12-11'
    ) AA INNER JOIN orderdetails BB ON AA.id = BB.order_id
    GROUP BY AA.id
) A INNER JOIN orders B USING (id);

Mysql – Correct way to write this view? Total sales/purchases/profits by day

I ended up doing it this way:

SELECT temp1.hday, 
temp1.user_id, 
temp1.total_buy, 
temp1.total_sell, 
coalesce(temp2.total_profit,0) as total_profit,
coalesce(temp3.margin,0) as margin
from

(
select 
calendar.days as hday, 
users.iduser as user_id, 
coalesce(sum(CASE WHEN transaction.transaction_type = 'Buy' THEN transaction.price_total END),0) as total_buy,
coalesce(sum(CASE WHEN transaction.transaction_type = 'Sell' THEN transaction.price_total END),0) as total_sell
-- (total_profit/total_buy) * 100 AS margin
from
calendar
JOIN transaction ON date(transaction.time) = calendar.days
JOIN users on transaction.user_iduser = users.iduser
GROUP BY calendar.days, users.iduser
order by calendar.days ASC
) temp1

left JOIN

(
select calendar.days as hday, 
users.iduser as user_id,
coalesce(sum(profit.profit_unit*profit.quantity_profit),0) as total_profit
from calendar
JOIN profit on date(profit.timestamp_sell) = calendar.days
JOIN users on  profit.users_idusers_OUT = users.iduser
GROUP BY calendar.days, users.iduser
order by calendar.days ASC
) temp2

ON temp1.hday = temp2.hday

left join

(
select (sum(profit.profit_unit*profit.quantity_profit)/sum(t1.price_unit*profit.quantity_profit))*100 as margin, calendar.days as hday
                from profit
                join transaction t1 on profit.transaction_idbuy_buy = t1.idbuy
                join transaction t2 on profit.transaction_idbuy_sell = t2.idbuy
                join users on t2.user_iduser = users.iduser
                join calendar on calendar.days = date(profit.timestamp_sell)

                GROUP BY calendar.days, users.iduser

) temp3

on temp2.hday = temp3.hday

I had to create 2 temporary tables. The first gathers information for purchases and sales with conditional aggregates while the second one contains the profit information. profit margin comes from merging again with a 3rd table.

There's probably easier ways of doing this, though

Best Answer

Related Solutions

Mysql – One to many join, Sum from both table

Mysql – Correct way to write this view? Total sales/purchases/profits by day

Related Question