CODEWITHSUNDEEP
mysqlsql
samuel
samuel

Reputation: 38

Count value from table column

first I have a table which is pivot looks like this

pivot_product_Id | productsxx_Id | category_Id | subcategory_Id | color_Id
---------------------------------------------------------------------------
       1         |        1        |      1    |      1         |   1
       2         |        1        |      1    |      1         |   2
       3         |        3        |      1    |      1         |   3
       4         |        4        |      1    |      2         |   4
       5         |        4        |      1    |      2         |   5
       6         |        2        |      2    |      4         |   6
       7         |        5        |      2    |      5         |   7

and I have color table like this 

color_Id   | color   |  color2
------------------------------------------
    1      |  black   | white
    2      |  blue    | orange
    3      |  white   | black
    4      |  purple  | black
    5      |  black   | green
    6      |  red     | black

and my question is in category ID 1 or 2 ... how many black color exist ? Counting from both color and color2 columns

and I tryed something like this but not geting the result I want and need help to create right query.

if(isset($shoes_post_var) || isset($nightwear_post_var)|| isset($outwear_post_var)){

                    $query3 = "SELECT count(*)
                FROM pivot
            JOIN category ON
                pivot.category_Id = category.category_Id
            JOIN subcategory ON
                pivot.subcategory_Id = subcategory.subcategory_Id       
            JOIN color ON
                pivot.color_Id = color.color_Id

            JOIN productsxx ON      
                pivot.productsxx_Id = productsxx.productsxx_Id
            WHERE  
             color IN ('$black') 
            or
             color2 IN ('$black')
            AND
            category IN ('$shoes_post_var','$nightwear_post_var','$outwear_post_var')
            GROUP BY pivot.color_Id  ASC ";
        $query5 = mysql_query($query3)or die(mysql_errno());
            $total = mysql_result($query5, 0);  
        echo ' '.'('.$total.')';}

Upvotes: 0

Views: 128

Answers (2)

peterm
peterm

Reputation: 92785

A possible solution

SELECT COUNT(*) total
  FROM pivot 
 WHERE category_id IN (1, 2)
   AND color_id IN
( 
  SELECT color_id
    FROM color
   WHERE color = 'black' 
      OR color2 = 'black'
)

Here is SQLFiddle demo

Upvotes: 1

Aleks G
Aleks G

Reputation: 57316

You only described two tables and asked about a query based on these two tables. This is a matter of a simple join with a simple selection - and a count - something like this:

SELECT count(1)
FROM pivot
JOIN color ON (pivot.color_id=color.color_id AND 'black' in (color.color, color.color2))
WHERE pivot.category_id = 1

Feel free to change the where clause for other categories.

However your existing code joins 5 tables and uses some other selection criteria. You really do need to ask the right question. Don't try to ask one thing while implying another.

Upvotes: 0

Related Questions