SQL Query with Join, Count and Where

Question

I have 2 tables and am trying to do one query to save myself some work.

Table 1: id, category id, colour
Table 2: category id, category name

I want to join them so that I get id, category id, category name, colour

Then I want to limit it so that no "red" items are selected (WHERE colour != "red") Then I want to count the number of records in each category (COUNT(id) GROUP BY (category id).

I have been trying:

SELECT COUNT(table1.id), table1.category_id, table2.category_name 
FROM table1 
INNER JOIN table2 ON table1.category_id=table2.category_id 
WHERE table1.colour != "red"

But it just doesn't work. I've tried lots of variations and just get no results when I try the above query.

John Woo · Accepted Answer · 2013-07-19 16:49:27Z

46

You have to use GROUP BY so you will have multiple records returned,

SELECT  COUNT(*) TotalCount, 
        b.category_id, 
        b.category_name 
FROM    table1 a
        INNER JOIN table2 b
            ON a.category_id = b.category_id 
WHERE   a.colour <> 'red'
GROUP   BY b.category_id, b.category_name

answered Jul 19, 2013 at 16:49

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

Comments

Nenad Zivkovic · Accepted Answer · 2013-07-19 16:50:21Z

8

SELECT COUNT(*), table1.category_id, table2.category_name 
FROM table1 
INNER JOIN table2 ON table1.category_id=table2.category_id 
WHERE table1.colour <> 'red'
GROUP BY table1.category_id, table2.category_name

answered Jul 19, 2013 at 16:50

Nenad Zivkovic

18.6k6 gold badges45 silver badges58 bronze badges

Comments

Kiran Shahi · Accepted Answer · 2018-01-25 11:29:42Z

2

I have used sub-query and it worked great!

SELECT *,(SELECT count(*) FROM $this->tbl_news WHERE
$this->tbl_news.cat_id=$this->tbl_categories.cat_id) as total_news FROM
$this->tbl_categories

edited Jan 25, 2018 at 11:29

Kiran Shahi

8,0109 gold badges41 silver badges77 bronze badges

answered Jan 25, 2018 at 11:18

Karthick

3812 silver badges7 bronze badges

Collectives™ on Stack Overflow

SQL Query with Join, Count and Where

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related