문제 설명
SQL 테이블 카운팅 및 조인 (SQL Table Counting and Joining)
I have Table A, Column 1.
This table has values such as:
1
2
3
3
4
4
4
5
I have Table B, Column 2.
Which lists certain values, like:
1
3
4
I need a query to Count each unique value in Table A, but ONLY if that value is present in Table B.
So with the above, the end result would be:
1 has a quantity of 1, 3 has a quantity of 2, and 4 has a quantity of 3.
My only problem is that I do not have this ability. Any help out there?
참조 솔루션
방법 1:
Based on your question, something like the following should solve your problem.
select b.column1, count(a.column2) from tableb as b inner join tablea as a on b.column1 = a.column2 group by b.column1
Since you wanted only records which are in both tables, I am using an inner join. Then I am just grouping by the ID found in tableb, and getting the count of rows in tablea.
Let me know if you have any problems.
For more information regarding inner join, see : http://www.w3schools.com/sql/sql_join_inner.asp, and for group by, see : http://www.w3schools.com/sql/sql_groupby.asp
방법 2:
I would use an INNER JOIN query with GROUP BY aggregate function
SELECT a.column1,
count(a.column1) as total
FROM tablea a
INNER JOIN tableb b
ON a.column1 = b.column2
GROUP BY a.column1
방법 3:
Try this
MsSql
Select Distinct Column1,Count(Column1) Over (Partition by Column1)
From Table1
Where Column1 IN (Select Column2 From Table2)
Fiddle Demo
MySQl
Select Column1,Count(Column1)
From Table1
Where Column1 IN (Select Column2 From Table2)
group by column1
Fiddle Demo
방법 4:
SELECT column1,COUNT(column1)
FROM table1
WHERE column1 IN
(SELECT DISTINCT column2 FROM table2)
GROUP BY column1
(by user3331453、Mez、Fabio、Vignesh Kumar A、124)