Mysql select query count & Distinct are not working properly-CodePudding

I am developing an eCommerce website using Laravel 8. I write the following script for find out total price & total quantity under a single order number. From following script getting the ERROR where is the problem please help me.
*At first I write row mysql then i will convert laravel query Builder.

SELECT COUNT (total_price) as totaPrice, COUNT (productqty) as proQnty
FROM (SELECT DISTINCT order_id FROM orderDetails)
LEFT JOIN ordertbl 
ON ordertbl.id = orderDetails.order_id;

CodePudding user response：

I guess you want to sum the prices and quantities, so use SUM() aggregate function.
Also you should do a LEFT join of ordertbl to orderDetails and not the other way around:

SELECT ot.id,
       SUM(od.total_price) AS totaPrice, 
       SUM(od.productqty) AS proQnty
FROM ordertbl ot LEFT JOIN orderDetails od
ON ot.id = od.order_id
WHERE ot.id = ?
GROUP BY ot.id;

Or, without a join:

SELECT SUM(total_price) AS totaPrice, 
       SUM(productqty) AS proQnty
FROM orderDetails 
WHERE order_id = ?;

Replace ? with the id of the order that you want.

CodePudding user response：

In Your raw in missing the tablename alis for the subquery .. Your raw query should be

SELECT COUNT(total_price) as totaPrice, COUNT(productqty) as proQnty
FROM (
    SELECT DISTINCT order_id FROM orderDetails
) T 
LEFT JOIN ordertbl ON ordertbl.id = T.order_id;