Solved

SQL-- count and if statement

Posted on 2015-01-31
3
147 Views
Last Modified: 2015-02-03
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
0
Comment
Question by:BBRRGG
3 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40581218
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
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40584314
create table #table (
    rating int,
    color varchar(30),
    color_type varchar(30)
    )
insert into #table
        select '83' as rating,      'Blue' as color,      'Regular' as color_type union all select
        '46','Black','Regular' union all select
        '34','Brown','Regular' union all select
        '45','Gray','Regular' union all select
        '34','Dark Blue','Dark' union all select
        '67','Light Blue','Light' union all select
        '85','Dark Brown','Dark' union all select
        '98','Light Brown','Light' union all select
        '45','Dark Gray','Dark' union all select
        '36','Light Gray','Light' union all select
        '27','Silver','Regular' union all select
        '75','Dark Silver','Dark' union all select
        '56','Light Silver','Light' union all select
        '75','Beige','Light' union all select
        '55','Yellow','Regular' union all select
        '66','White','Light' union all select
        '78','Red','Regular' union all select
        '34','Green','Regular' union all select
        '24','Orange','Regular' union all select
        '2','Maroon','Regular' union all select
        '32','Purple','Regular' union all select
        '82','Dark Purple','Dark' union all select
        '32','Light Purple','Light' union all select
        '65','Dark red','Dark' union all select
        '36','Light Red','Light' union all select
        '75','Dark Orange','Dark' union all select
        '96','Light Orange','Light'


DECLARE @color varchar(30)
SET @color = '%blue%'

;WITH cteColorCount AS
(
    SELECT SUM(1) AS Color_Count
    FROM #table
    WHERE Color LIKE @color
)
SELECT Rating, Color, Color_Type
FROM #table
CROSS JOIN cteColorCount
WHERE
    Color LIKE CASE WHEN Color_Count < 3 THEN '%' ELSE @color END
ORDER BY
    CASE WHEN color LIKE @color THEN 1 ELSE 2 END

SET @color = '%yellow%'

;WITH cteColorCount AS
(
    SELECT SUM(1) AS Color_Count
    FROM #table
    WHERE Color LIKE @color
)
SELECT Rating, Color, Color_Type
FROM #table
CROSS JOIN cteColorCount
WHERE
    Color LIKE CASE WHEN Color_Count < 3 THEN '%' ELSE @color END
ORDER BY
    CASE WHEN color LIKE @color THEN 1 ELSE 2 END
0
 

Author Closing Comment

by:BBRRGG
ID: 40587891
Great solution!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now