Link to home
Start Free TrialLog in
Avatar of BBRRGG
BBRRGG

asked on

SQL-- count and if statement

There are 3 fields in the attached:
Rating, Color, Color_Type.  

If I run the following code against the table, it returns the 3 records that contain 'blue'  (blue, light blue, dark blue).

Select Rating, Color, Color_Type
from Table
where Color like ('%blue%')

I need to add a condition that does a count on the total # of records, and adds an "if" condition.  The condition is:
if there are < 3 records in the output, remove the "where color like " clause & show the entire output.  In the full list of colors, sort that color to the top.  

else if there are 3 or greater records with the specified color (3+ records in the output), show just those records.  The SQL for this condition is simply the original SQL:
Select Rating, Color, Color_Type
from Table
where Color like ('%blue%')

For example, for color like ('%blue%'), it would just return the 3 blue records.

But for yellow, since there's only 1 yellow record, it would return all 27 records, with yellow sorted to the top.



Ranking      Color      Color_type
83      Blue      Regular
46      Black      Regular
34      Brown      Regular
45      Gray      Regular
34      Dark Blue      Dark
67      Light Blue      Light
85      Dark Brown      Dark
98      Light Brown      Light
45      Dark Gray      Dark
36      Light Gray      Light
27      Silver      Regular
75      Dark Silver      Dark
56      Light Silver      Light
75      Beige      Light
55      Yellow      Regular
66      White      Light
78      Red      Regular
34      Green      Regular
24      Orange      Regular
2      Maroon      Regular
32      Purple      Regular
82      Dark Purple      Dark
32      Light Purple      Light
65      Dark red      Dark
36      Light Red      Light
75      Dark Orange      Dark
96      Light Orange      Light
If-SQL.xlsx
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

I would put the sql into a stored proc
There i would define a table variable, and insert the results of the query with the filter into that table.
Then using a check on @@rowcount variable, decide if you want to add the other rows to that table also or not.
At the end rreturn the table results with the proper order by
Hope this helps already
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of BBRRGG
BBRRGG

ASKER

Great solution!