CODEWITHSUNDEEP
sql
user7513545
user7513545

Reputation: 21

SQL Query with different aggregation for count

Publisher 

pub_id  title_id    city

1         1           NY
1         2           NY
2         3           CA
3         4           VA

Titles 

title_id    price   genre
1            10     Horror
2             5     Bio
3            50     Science

Question:
Create a SQL query that gives
-pub_id
-# titles
-# titles with Horror genre

I have been having hard time writing this SQL query and can't figure out how to include both #titles and #titles with horror genre in the same query. I would appreciate any help on this. Thank you.

Query I have tried so far( don't know how to incude titles with horror genre):

select a.pub_id, count(a.titles) 
from publisher a 
  left join titles b on a.title_id = b.title_id group by a.pub_id

If I use having then I won't be able to calculate the total number of titles.

Upvotes: 2

Views: 56

Answers (2)

Saif
Saif

Reputation: 2679

you can use this query to get expected output 

select t.pub_id, COUNT(t.title_id) as title_id, t2.genre
from table1 t
 inner join 
 table2 t2 
on t.title_id = t2.title_id and t2.genre like 'Horror%'
group by t.pub_id, t2.genre

Note: change table1 and table2 names into your table name

the output is shown here

enter image description here

Upvotes: 0

K D
K D

Reputation: 5989

use following query to achieve your results

select 
   pub_id,
   count(*) as [titles], 
   SUM(CASE WHEN genre='horror' then 1 else 0 END) as [horror titles] 
from Publisher a
inner join titles b on a.title_id=b.title_id
group by
pub_id

you can use CASE statements to do this

Upvotes: 3

Related Questions