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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great solution!
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