在SQL SELECT语句中使用多WHERE条件并修复错误查询使其可执行

Got it, let's get this query working properly. The core issue here is that you're trying to use WHERE clauses directly inside aggregate functions—this isn't valid SQL syntax. Instead, we need to use conditional aggregation with CASE WHEN to calculate your metrics based on the ART_ID values.

Here's the corrected query:

SELECT 
    'app' AS appkey,
    'arival' AS layer, 
    'fmcg' AS sets,
    ID AS HOME_ID, 
    INVO_DATE AS INVOICE_DATE, 
    -- Count distinct concatenated values only when ART_ID isn't 1
    COUNT(DISTINCT CASE WHEN ART_ID != '1' THEN CONCAT(P_DATE, TP_ID) END) AS dispatch,
    -- Sum SALES_product only for rows where ART_ID is 1
    SUM(CASE WHEN ART_ID = '1' THEN SALES_product END) AS product_sum,
    -- Sum SALES_area only for rows where ART_ID isn't 1
    SUM(CASE WHEN ART_ID != '1' THEN SALES_area END) AS area_SALES
FROM sales
GROUP BY HOME_ID, INVOICE_DATE, appkey, layer, sets

Key Changes Explained: #

• Replaced invalid WHERE clauses with CASE WHEN: This is the standard way to perform conditional aggregation in SQL. When the CASE condition is met, it returns the value to aggregate; when not, it returns NULL, which aggregate functions (like SUM and COUNT) automatically ignore.
• Used SQL-standard single quotes: While some databases allow double quotes for string literals, single quotes are universally supported and follow SQL standards.
• Added constant columns to GROUP BY: Strict SQL requires all non-aggregated columns in the SELECT clause to be included in GROUP BY. Even though your appkey, layer, and sets are fixed values, including them avoids errors in databases that enforce strict mode (like PostgreSQL, MySQL with ONLY_FULL_GROUP_BY enabled).
• Optional: Keep || if your database supports it: If you're using a database like Oracle or PostgreSQL that allows || for string concatenation, you can replace CONCAT(P_DATE, TP_ID) back with P_DATE||TP_ID—both work, but CONCAT is more portable across different SQL dialects.

内容的提问来源于stack exchange，提问作者batman_special