Add a condition with alias value

HareaCostea

I tried to add an additional condition

SELECT
   ROUND(SUM((cus.amount_product/1000)),1) as TOTAL_N,
   ROUND(SUM((cus.amount_product/1000)),1) as TOTAL_PRODUCT
FROM customs AS cus
LEFT JOIN customs_products AS cp ON cp.id = cus.customs_product_id
   WHERE cp.product IN (101,103,104,106)
   AND TOTAL_PRODUCT  >= 1

The problem is in this TOTAL_PRODUCT. I tried to do that in multiple ways :

1. AND (ROUND(SUM((cus.amount_product/1000)),1)) > 1 --> NOT WORKING
2. AND TOTAL_PRODUCT  >= 1 --> NOT WORKING

Can you help me please ? What I'm doing worng

Gordon Linoff

A HAVING clause does what you want, but your query just doesn't make sense. You have the same expression twice. Even if that is a typo, the WHERE clause is turning the LEFT JOIN into an inner join. I suspect you may really want:

SELECT COUNT(cus.customs_product_id) as TOTAL_N,
       ROUND(SUM((cus.amount_product/1000)), 1) as TOTAL_PRODUCT
FROM customs_products cp LEFT JOIN
     customs cus
     ON cp.id = cus.customs_product_id
WHERE cp.product IN (101, 103, 104, 106)
HAVING TOTAL_PRODUCT >= 1;

I suspect that you might actually want a GROUP BY in the query as well.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related