Mysql – Count two different columns from two different tables and throw into a single variable

countMySQLselect

I have two tables in my project: "cats" and "items". Every single item in "items" table has the value "item_cat" (column), which represents the ID of actual category, where that item is assigned to. And every single category in "cats" table has its unique ID.

When I add a new item, I can choose the category ID which can only be the one of categories from "cats" table: "music" (id=1), "books" (id=2) or "movies" (id=3). Items stored: "Pulp fiction" (id=1), "Mein kampf" (id=2), "Gone with the wind" (id=3), "Les miserables" (id=4), "The Holy Bible" (id=5) and "The best of Tina Turner" (id=6). Items 1, 3 and 4 belong to category 3, items 2 and 5 belong to category 2, and item 6 belongs to category 1 (I hope I'm still understandable 😉 ).

Now I want to count how many items are assigned to every category. Is it possible to use "SELECT COUNT" in some combination, to get such a result as an output: cat1 = 1 (item), cat2 = 2 (items), cat3 = 3 (items)? How to do this?

Thank you in advance for your help!

Best Answer

SELECT item_cat, COUNT(*)
    FROM items
    GROUP BY item_cat;

(I see no need for a CASE.)

Now, assuming that the table cats has a name column, you might want:

SELECT ( SELECT name FROM cats WHERE id = i.item_cats ) AS "Category",
       COUNT(*) AS "Number of items in that Category"
    FROM items
    GROUP BY item_cat;

Related Solutions

Mysql match on two columns and get count

@Abdul's answer just needs HAVING COUNT(*) > 1

The query shold look like this now

SELECT COUNT(*), col1 , col2 FROM test GROUP BY col1,col2 HAVING COUNT(*) > 1;

Here is the execution of it:

mysql> create table test(col1 varchar(20),col2 varchar(20));
Query OK, 0 rows affected (0.33 sec)

mysql> INSERT INTO test VALUES('Joe','April'),('Fred','May'),
    -> ('Sam','April'),('Joe','April'),('Mark','Jan'),('Joe','Dec');
Query OK, 6 rows affected (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(*), col1 , col2 FROM test GROUP BY col1,col2 HAVING COUNT(*) > 1;
+----------+------+-------+
| COUNT(*) | col1 | col2  |
+----------+------+-------+
|        2 | Joe  | April |
+----------+------+-------+
1 row in set (0.24 sec)

mysql>

If you do not care for the count, remove it from the SELECT clause

mysql> SELECT col1 , col2 FROM test GROUP BY col1,col2 HAVING COUNT(*) > 1;
+------+-------+
| col1 | col2  |
+------+-------+
| Joe  | April |
+------+-------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!

Sql-server – How to count the rows from 1 table but from two different column

You can use a cross apply and values to create a row for each value and an extra column in the values clause to keep track of what value is a major and what value is a minor.

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE ApplicantCourseDetail(
    [Major] [nvarchar](50) NULL,
    [MinorI] [nvarchar](50) NULL
)
INSERT INTO ApplicantCourseDetail
           ([Major]
           ,[MinorI])
     VALUES
           ('Chemistry','History'),
('English','Chemistry'),
('Botany','Zoology'),
('History','English'),
('Chemistry','Chemistry'),
('English','Philosophy'),
('History','English')

Query 1:

select T.Value,
       count(case when T.IsMajor = 1 then A.Major end) as MajorCount,
       count(case when T.IsMajor = 0 then A.MinorI end) as MinorCount
from dbo.ApplicantCourseDetail as A
  cross apply (values (A.Major, 1),(A.MinorI, 0)) as T(Value, IsMajor)
group by T.Value;

Results:

|      VALUE | MAJORCOUNT | MINORCOUNT |
|------------|------------|------------|
|     Botany |          1 |          0 |
|  Chemistry |          2 |          2 |
|    English |          2 |          2 |
|    History |          2 |          1 |
| Philosophy |          0 |          1 |
|    Zoology |          0 |          1 |

Best Answer

Related Solutions

Mysql match on two columns and get count

Give it a Try !!!

Sql-server – How to count the rows from 1 table but from two different column

Related Question