MYSQL retrieve data dependent on rows returned

Question

I am working on a mysql query that will filter out certain occurrences dependent on how many rows are returned.

I am trying to filter out any support categories when the number of rows returned are 1, however leave the support category in when the result set turned is more than 1.

I originally had this idea however it seems as if it will not work.

SELECT stockmaster.description, SUM(salesorderdetails.quantity), stockmaster.categoryid         as qty 
FROM salesorderdetails, stockmaster 
where salesorderdetails.stkcode=stockmaster.stockid 
and orderno='5222'
group by stockmaster.description
HAVING CASE WHEN stockmaster.categoryid = 'S&M' THEN COUNT(*) >= 2 ELSE COUNT(*) = 1   END

Any help will be gratefully accepted.

provide some simple data by http://sqlfiddle.com/ and expected result — jaczes, Jul 19 '13 at 12:17

score 0 · Answer 1 · answered Jul 19 '13 at 12:21

Try this

SELECT *
FROM
(

SELECT stockmaster.description, 
       SUM(salesorderdetails.quantity), 
       stockmaster.categoryid         as qty ,
       COUNT(*) AS count
FROM salesorderdetails, stockmaster 
where salesorderdetails.stkcode=stockmaster.stockid 
and orderno='5222'
group by stockmaster.description
HAVING CASE WHEN stockmaster.categoryid = 'S&M' 

) MAIN_DATA

WHERE MAIN_DATA.count >1

MYSQL retrieve data dependent on rows returned

1 Answers1