MySQL - Doing addition from "subquery field" in SELECT using alias

DenjanD
April 11, 2023
239 views
1 vote
2 Answers

I would like to add some SUMs from subqueries in the SELECT into another new field. Here is the query that I am currently using:

SELECT DAYNAME(date) AS day, DATE_FORMAT(date, "%d-%m-%Y") AS date,
    (SELECT SUM(qty) AS product1_shift1
    FROM bill_details 
        JOIN bills ON bills.bill_id=bill_details.bill_id
    WHERE bills.shift_id='48c73e3d-57de-4fea-aae4-7222d7e6afed'
    AND bills.date=bills_master.date) AS product1_shift1,
    (SELECT SUM(qty) AS product1_shift2
    FROM bill_details 
        JOIN bills ON bills.bill_id=bill_details.bill_id
    WHERE bills.shift_id='463ec995-cfad-46ff-8e65-3115e6e651c5'
    AND bills.date=bills_master.date) AS product1_shift2,
    (SELECT SUM(qty) AS product1_shift3
    FROM bill_details 
        JOIN bills ON bills.bill_id=bill_details.bill_id
    WHERE bills.shift_id='0a792c5f-84a6-4534-818c-427cfe8cd3ca'
    AND bills.date=bills_master.date) AS product1_shift3
FROM bills bills_master
GROUP BY bills_master.date

And here is the output:

Notice that the product1_shift1 field is a subquery result. I would like to do the product1_shift1+product1_shift2+prdduct1_shift3. I keep getting the

Unknown column ‘product1_shift1’ in ‘field list’

error when I tried to add another SELECT field like

product1_shift1+product1_shift2+product1_shift3.

How can I solve this? Thank you.

Answers

You don’t need the subqueries.
Use a LEFT join of bills to bill_details and conditional aggregation:

SELECT DAYNAME(b.date) AS day, 
       DATE_FORMAT(b.date, '%d-%m-%Y') AS date,
       SUM(CASE WHEN b.shift_id = '48c73e3d-57de-4fea-aae4-7222d7e6afed' THEN bd.qty END) AS product1_shift1,
       SUM(CASE WHEN b.shift_id = '463ec995-cfad-46ff-8e65-3115e6e651c5' THEN bd.qty END) AS product1_shift2,
       SUM(CASE WHEN b.shift_id = '0a792c5f-84a6-4534-818c-427cfe8cd3ca' THEN bd.qty END) AS product1_shift3,
       SUM(bd.qty) AS product1_shift_total
FROM bills b LEFT JOIN bill_details bd
ON bd.bill_id = b.bill_id
AND b.shift_id IN ('48c73e3d-57de-4fea-aae4-7222d7e6afed', '463ec995-cfad-46ff-8e65-3115e6e651c5', '0a792c5f-84a6-4534-818c-427cfe8cd3ca')
GROUP BY b.date;

You can put your sql query in a CTE then calculate your total as follows :

with cte as (
  SELECT DAYNAME(date) AS day, DATE_FORMAT(date, "%d-%m-%Y") AS date,
    (
      SELECT SUM(qty) as sum_qty
      FROM bill_details 
      JOIN bills ON bills.bill_id=bill_details.bill_id
      WHERE bills.shift_id='48c73e3d-57de-4fea-aae4-7222d7e6afed'
      AND bills.date=bills_master.date
    ) AS product1_shift1,
    (
      SELECT SUM(qty) as sum_qty
      FROM bill_details 
      JOIN bills ON bills.bill_id=bill_details.bill_id
      WHERE bills.shift_id='463ec995-cfad-46ff-8e65-3115e6e651c5'
      AND bills.date=bills_master.date
    ) AS product1_shift2,
    (
       SELECT SUM(qty) as sum_qty
       FROM bill_details 
       JOIN bills ON bills.bill_id=bill_details.bill_id
       WHERE bills.shift_id='0a792c5f-84a6-4534-818c-427cfe8cd3ca'
       AND bills.date=bills_master.date
    ) AS product1_shift3
  FROM bills bills_master
  GROUP BY bills_master.date
)
select *, COALESCE(product1_shift1,0) + COALESCE(product1_shift2,0) + COALESCE(product1_shift3,0) as total
from cte

Please signup or login to give your own answer.

Click here to cancel reply.

MySQL – Doing addition from "subquery field" in SELECT using alias

Answers