SQL - Count two columns together-CodePudding

I'm trying to count multiple columns as one column. For example:

Table 1 (Books):

ID	BookName	Genre	SubGenre
1	Name1	1	3
2	Name2	2	1
3	Name3	4	2

Table 2 (Genre):

ID	Genre
1	Horror
2	Drama
3	Romance
4	Sci-Fi

I want to be able to count the genre and subgenre as one to create a table of:

Result:

Genre	Count
Horror	2
Drama	2
Romance	1
Sci-Fi	1

Any help would be really appreciated. Thanks

CodePudding user response：

Try below query-

SELECT t.Genre, Sum(t.cg) AS Count
FROM (

SELECT t2.Genre, Count(t1.Genre) AS cg
FROM Table2 as t2 LEFT JOIN Table1 as t1 ON t2.ID = t1.Genre
GROUP BY t2.Genre

UNION ALL 

SELECT t2.Genre, Count(t1.SubGenre) AS cg
FROM Table2 as t2 LEFT JOIN Table1 as t1 ON t2.ID = t1.SubGenre
GROUP BY t2.Genre

) as t GROUP BY t.Genre;