CODEWITHSUNDEEP
sql
Todd
Todd

Reputation: 97

SQL: 2 Counts using joins?

I have these 2 tables:

Table: Unit

UnitID | Title   
1        Unit 1
2        Unit 2
3        Unit 3

Table: Piece

PieceID | UnitID | Category
1         1        A
2         1        A
3         1        B
4         2        A
5         3        B

What I need to do is show a count of the total units containing Piece rows with Category A, as well as the total amount of Piece table rows with Category A (regardless of unitid). So using the data above, the result would be 2 units, 3 Piece rows.

I could do this with two statements, but I would like to do it one.

Any suggestions from craftier folks than I?

Upvotes: 2

Views: 100

Answers (2)

Guffa
Guffa

Reputation: 700670

Filter out the pieces with the correct category, then count the units distinctly:

select count(distinct UnitId) as Units, count(*) as Pieces
from Piece
where Category = 'A'

Upvotes: 2

user359040
user359040

Reputation:

Try:

select count(distinct UnitID) total_units, count(*) total_rows
from Piece
where Cateory = 'A';

Upvotes: 2

Related Questions