CODEWITHSUNDEEP
mysqlsqlcount
user3051207
user3051207

Reputation:

SQL Counting Multiple Rows as One Query

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.

Upvotes: 0

Views: 63

Answers (1)

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

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

Upvotes: 2

Related Questions