Use multiple where conditions in SQL select statement

Question

I have a SQL query as below(which is wrong) where I need to adjust the where condition as per the conditions shared.

select 
    'app' as appkey,'arival' as layer,
    'fmcg' as sets,ID as HOME_ID,
    INVO_DATE as INVOICE_DATE,
    count(distinct(P_DATE||TP_ID)) 
where 
    ART_ID != "1" as dispatch,
    sum(SALES_product) 
where 
    ART_ID = "1" as product_sum, sum(SALES_area) 
where  
    ART_ID != "1" as area_SALES 
from 
    sales group by HOME_ID, INVO_DATE

I request to please help in making the query executable.

Please provide sample data, desired results, and a clear explanation of what you want to accomplish. Your non-working query is not clear on what you intend. — Gordon Linoff, Sep 09 '21 at 11:08
Your query doesn't make any sense. Also, are you REALLY using SQL Server 2008?! — DavidG, Sep 09 '21 at 11:10
Why where clause is before from? It is wrong. Please provide some sample data with table structure and expected output. — Rahul Biswas, Sep 09 '21 at 11:16
@RahulBiswas yes i mentioned that its a wrong query i have written hence wanted your help in restructuring the query — batman_special, Sep 09 '21 at 11:19
A valid T-SQL SELECT is always built up like `SELECT (list of columns) FROM (table name) JOIN (table or view name) ON (some condition) WHERE (condition1) AND/OR (condition2) AND/OR (condition3).........` - your `where` clause all over the query don't make any sense at all ..... — marc_s, Sep 09 '21 at 11:22
here is the requirement i have these are the SQL conditional statements i have to use there are different where conditions which i need to use count(distinct(P_DATE||TP_ID)) where ART_ID!="1" -->dispatch, sum(SALES_product) where ART_ID="1" --> product_sum, sum(SALES_area) where ARTI_ID!="1"--> area_SALES group by HOME_ID,INVO_DATE — batman_special, Sep 09 '21 at 11:22
@marc_s my statement has multiple where conditions which needs to be taken care in one sql statement here is the requirement i have these are the SQL conditional statements i have to use there are different where conditions which i need to use count(distinct(P_DATE||TP_ID)) where ART_ID!="1" -->dispatch, sum(SALES_product) where ART_ID="1" --> product_sum, sum(SALES_area) where ARTI_ID!="1"--> area_SALES group by HOME_ID,INVO_DATE — batman_special, Sep 09 '21 at 11:25
@batman_special, heed the advice given by Gordon and provide sample source data and expected results. Your question will likely be closed if you don't do that. The atempt with invalid syntax and the explanation in comments (which should be in the question) is unclear. — Dan Guzman, Sep 09 '21 at 11:29
Why are you using the Oracle text concatenator and this is marked SQL Server? — SteveC, Sep 09 '21 at 12:02

score 1 · Answer 1 · answered Sep 09 '21 at 11:40

WHERE condition logic is converted in CASE statement as per requirement.

SELECT 'app' as appkey
     ,'arival' as layer
     , 'fmcg' as sets
     , ID as HOME_ID
     , INVO_DATE as INVOICE_DATE
     , COUNT(DISTINCT CASE WHEN ART_ID != '1' THEN (P_DATE||TP_ID) END) dispatch
     , SUM(CASE WHEN ART_ID = '1' THEN SALES_product END) product_sum
     , SUM(CASE WHEN ART_ID != '1' THEN SALES_area END) area_SALES 
FROM sales 
GROUP BY ID, INVO_DATE

Thank you so very much, Rahul Biswas...this helped actually. — batman_special, Sep 09 '21 at 12:24

Use multiple where conditions in SQL select statement

1 Answers1