SQL Counting Multiple Rows as One Query

Question

Can someone help me. Here's what I'm trying to do.
I have a table:

**tblColors**
id    color_name
1      red
2      blue
3      white
4      white
5      blue
6      red
7      blue
8      white
9      red
10     blue

For example I accept 3 user inputs which are:
1. red 2. blue 3. white

I want to count how many sets of these 3-colors are present in my database. (red-blue-white)
In my sample database the answer should be:

**tblColors**
id    color_name
------------------>id 1-3 is my first set of (red-blue-white)
1      red
2      blue
3      white
------------------

4      white
5      blue

-------------------------->id 6-8 is my second set
6      red
7      blue
8      white
--------------------------
9      red
10     blue

I have 2 (red-blue-white) set in my database so the result should be: 2
Sorry the description of the problem is not that clear but I hope you get the picture.

Why don't you have a unique index on color_name? What is the purpose of ** in your answer? I don't see logical groups. Do you want to have the common maximum count of these colors? — Tim Schmelter
– Tim Schmelter, Commented Aug 8, 2014 at 7:46
So you want to count if there is a sequence of red, blue and white? — deterministicFail
– deterministicFail, Commented Aug 8, 2014 at 7:49
@TimSchmelter hi, I edited my post. pls review the answer to my post to see what i want to count. — user3051207
– user3051207, Commented Aug 8, 2014 at 7:53

Giannis Paraskevopoulos · Accepted Answer · 2014-08-08 07:49:03Z

2

SELECT COUNT(*)
FROM   tblColors T1
       LEFT JOIN tblColors T2
           ON T1.id = T2.id - 1
       LEFT JOIN tblColors T3
           ON T2.id = T3.id - 1
WHERE  T1.color_name + '-' +
       T2.color_name + '-' +
       T3.color_name = 'red-blue-white'

Fiddler Demo

answered Aug 8, 2014 at 7:49

Giannis Paraskevopoulos

18.5k2 gold badges52 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Tim Schmelter Over a year ago

+1 even if it's not clear whether white-blue-red should be counted or not.

Giannis Paraskevopoulos Over a year ago

@TimSchmelter Based on OPs eaxample, the order of the colors does matter.

Tim Schmelter Over a year ago

Yes, but in most cases sample data on SO is inappropriate. So as long OP doesn't confirm this it's not set in stone. OP stated: "I want to count how many sets of these 3-colors are present in my database" A set is by definition unordered: "the order in which the elements of a set are listed is irrelevant (unlike for a sequence or tuple)"

user3051207 Over a year ago

hi @GiannisParaskevopoulos, thanks a lot this is exactly what im trying to do. Thank you so much prof.

user3051207 Over a year ago

@TimSchmelter sorry for my unclear description my english is not so good, im sure you could've answered if the problem is clear. thanks anyway.

|

Collectives™ on Stack Overflow

SQL Counting Multiple Rows as One Query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related