CODEWITHSUNDEEP
sql
Haj Mohamed A
Haj Mohamed A

Reputation: 83

if else condition using sql query

I have problem to generate SQL query for below table. Here is my table:

County          | Store     | Stock | Display | Designation
--------------- | --------- | ----- | ------- | ------------
USA             | USD       | 1     | Yes     | Merchandiser
USA             | USD       | 2     | Yes     | Promoter

I want to be result like this

County          | Store     | Stock | Display | Designation
--------------- | --------- | ----- | ------- | ------------
USA             | USD       | 2     | Yes     | Merchandiser
USA             | USD       | 2     | Yes     | Promoter

The Scenario is if Designation is Promoter, use both Display and Stock data. If Designation is from Merchandiser, use data from Promoter for Stock data How can i achieve this?

Upvotes: 2

Views: 136

Answers (4)

Poornima
Poornima

Reputation: 94

Please try the below code to create temporary table

--===== If the test table already exists, drop it
 IF OBJECT_ID('TestDB..#mytable','U') IS NOT NULL
     DROP TABLE #mytable
 --===== Create the test table with 
 CREATE TABLE #mytable 
        (
        Country varchar(20),
        Store varchar(20),
        Stock int,
        Display varchar(5),
        Designation varchar(20)
        )
   --===== Insert the test data into the test table
 INSERT INTO #mytable 
       (Country, Store, Stock, Display, Designation)


 SELECT 'SG','a','2','YES','Merchandiser' UNION ALL
 SELECT 'SG','a','4','YES','Promoter'

Now use the below above query

SELECT Country, 
       Store , 
       (CASE WHEN (Designation = 'Merchandiser') THEN (SELECT SUM(STOCK) FROM #mytable WHERE Country = Country AND Designation = 'Promoter' GROUP BY Country) ELSE STOCK END) AS "stock",
       Display, 
       Designation FROM #mytable

Upvotes: 2

Sahathulla
Sahathulla

Reputation: 324

I think you are expecting query like this


    Select a.Country, a.Store
    , (CASE WHEN a.Designation = "Merchendiser" THEN (select  Top 1 b.Stock from YourTable b where b.Designation = "Promoter" and b.Country = a.Country and b.Store = a.Store order by id desc) ELSE a.Stock) as Stock
    , a.Display
    , a.Designation
    FROM YourTable a WHERE b.Designation = "Promoter"

Upvotes: 2

TOUZENE Mohamed Wassim
TOUZENE Mohamed Wassim

Reputation: 712

Try This 

SELECT County, 
           Store , 
           (CASE WHEN (Designation = 'Merchandiser') THEN (SELECT SUM(STOCK) FROM TABLE WHERE County = County AND Designation = 'Promoter' GROUP BY County) ELSE STOCK END) AS "stock",
           Display, 
           Designation
FROM table

This request give you the needed result.

Upvotes: 2

Cenderze
Cenderze

Reputation: 1222

Select a.Country, a.Store
, Stock = CASE WHEN a.Designation = "Merchendiser" THEN b.Stock ELSE a.Stock
, Display = CASE WHEN a.Designation = "Merchendiser" THEN b.Display ELSE a.Display
, a.Designation
FROM YourTable a LEFT JOIN YourTable b WHERE b.Designation = "Promoter"

may do the trick. Unable to test at the moment.

Edit: I see that you havent stated which SQL you use (i.e. SQL server, MySQL, PostgreSQL etc., so depending on that this solution may not be able to run).

Upvotes: 2

Related Questions