SQLTeam.com | Weblogs | Forums

Problem with group by

sql2008

#1

hi everyone, i have a problem with a sentence (i'm kind of new with sql) hope someone can help me.
Here it is, with this query:

select FIELD1, FIELD2, 
CASE FIELD3 WHEN 6 THEN FIELD3 ELSE 0 END AS [FIELD3], 
CASE FIELD3 WHEN 1 THEN FIELD3 ELSE 0 END AS [FIELD4]
from [TABLE] 
where 
FIELD1 = 'ARTICULOS' AND (FIELD3 = 1 or FIELD3 = 6) 
group by 
FIELD1, 
FIELD2, 
FIELD3
order by 
FIELD2;

i get the followin result:

in database dome rows have same code (field2) because ther are in stock (6) or in store (1) and some products are in both so i get some duplicated codes, i need an output showing in a single row if ther are register in both stock and store, any help i'll be thankfull.


#2

select FIELD1, FIELD2,
MAX(CASE FIELD3 WHEN 6 THEN FIELD3 ELSE 0 END) AS [FIELD3],
MAX(CASE FIELD3 WHEN 1 THEN FIELD3 ELSE 0 END) AS [FIELD4]
from [TABLE]
where
FIELD1 = 'ARTICULOS' AND (FIELD3 = 1 or FIELD3 = 6)
group by
FIELD1,
FIELD2
order by
FIELD2;


#3

thanks.