SQLTeam.com | Weblogs | Forums

Sql case query with different condition with a single column


#1

I HAVE 2 ITEM ,ITEM NAME 'CHAIR' AND 'BASKET' AND A QUANTITY...I NEED THAT WHERE QUANTITY > 20 AND ITEM='CHAIR'; AND FOR ITEM='BASKET' ,QUANTITY >'09' SHOULD COME IN OUTPUT..

I FAILED ,NULLS ARE COMING..

I NEED THAT FOR ITEM CHAIR DQTY SHOULD SHOW >20 AND FOR ITEM BASKET DQTY SHOULD BE > 9,,IT SHOULD BE SHOWED IN MY OUTPUT..I DON'T WANT OTHER DQTY NUMBER IN MY OUTPUT

USING SQL-server-2014

this is giving me NUll ,not desire output

I wrote this query

"SELECT CASE WHEN op.dqty > '20' AND ti.SubCategoryName ='CHAIR' THEN ti.SubCategoryName WHEN op.dqty > '9' AND ti.SubCategoryName ='BASKET' THEN ti.SubCategoryName ELSE NULL END AS SubCategoryName , TI.SubCategoryName,op.site,op.sasitename,op.symn,op.dqty,cou‌​nt(distinct(orna)) as orna from ABCD op inner join item ti WHERE TI.SubCategoryName in ('CHAIR' ,'BASKET') GROUP BY TI.SubCategoryName,op.site,op.sasitename,op.symn,op.dqty"


#2

Please don't post in ALL CAPITALS. It's really hard to read.

Can you post a sample of your data? I think you are missing a WHERE clause

Please format your query before posting e.g. this is more readable:

SELECT CASE 
		WHEN op.dqty > '20'
			AND ti.SubCategoryName = 'CHAIR'
			THEN ti.SubCategoryName
		WHEN op.dqty > '9'
			AND ti.SubCategoryName = 'BASKET'
			THEN ti.SubCategoryName
		ELSE NULL
		END AS SubCategoryName
	,TI.SubCategoryName
	,op.site
	,op.sasitename
	,op.symn
	,op.dqty
	,cou‌​nt(DISTINCT (orna)) AS orna
FROM ABCD op
INNER JOIN item ti
WHERE TI.SubCategoryName IN (
		'CHAIR'
		,'BASKET'
		)
GROUP BY TI.SubCategoryName
	,op.site
	,op.sasitename
	,op.symn
	,op.dqty