CODEWITHSUNDEEP
sqldatabaseoracle-database
wforwumbo
wforwumbo

Reputation: 25

Sum two counts from 2 different tables in sql

I have two tables on db:

1. memberOne

memberName | gender
===================
Jack       | M
Steve      | M
Audrey     | F


2. memberTwo

memberName | gender
===================
Sarah      | F
Steve      | M
Audrey     | F
Alvin      | M

I want to display this view:

Gender         | Total
=======================
M              | 4
F              | 3

I performed this code

SELECT t.Gender, COUNT(t.Gender) Total FROM memberOne t
GROUP BY t.Gender
UNION ALL
SELECT d.Gender, COUNT(d.Gender) Total FROM memberTwo d
GROUP BY d.Gender
;

And this is what I got:

Gender       |  Total
------------- ----------
M                      2 
F                      1 
M                      2 
F                      2

How can I sum the total of M and F from each table? Should I use condition to check the gender? Any helps would be appreciated, thanks.

Upvotes: 1

Views: 6667

Answers (5)

Anthony McGrath
Anthony McGrath

Reputation: 802

One way to accomplish your goal working off your initial query would be:

select Gender, sum(Total) Total
From (your existing query) q
group by q.Gender

A different way would be:

select Gender, Count(Gender) Total
from
( select Gender from membeOne
  Union all
  Select Gender from memberTwo ) q
group by q.Gender

Upvotes: 0

Juan
Juan

Reputation: 5589

Wrap your last query in another query that sums the count of M and F.

SELECT
    G, SUM(Total) 
FROM
    (SELECT  
         t.Gender G, COUNT(t.Gender) Total 
     FROM
         memberOne t
     GROUP BY 
         t.Gender

    UNION ALL

    SELECT 
        d.Gender G, COUNT(d.Gender) Total 
    FROM 
        memberTwo d
    GROUP BY 
        d.Gender) 
GROUP BY 
    gender

Upvotes: 1

jace
jace

Reputation: 1674

SELECT n.Gender, COUNT(n.Gender) Total FROM
(
SELECT t.Gender, COUNT(t.Gender) Total FROM memberOne t
GROUP BY t.Gender
UNION ALL
SELECT d.Gender, COUNT(d.Gender) Total FROM memberTwo d
GROUP BY d.Gender
) n
GROUP BY n.Gender

reiterating select and group by after last query

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

One approach here would be to union together only the genders from the two tables, and then do a single aggregation to get the male and female counts.

SELECT
    gender,
    COUNT(*) AS total
FROM
(
    SELECT gender
    FROM memberOne
    UNION ALL
    SELECT gender
    FROM memberTwo
) t
GROUP BY gender
ORDER BY gender DESC

Demo here:

Rextester

Upvotes: 1

Ravi
Ravi

Reputation: 31397

You need to use UNION ALL and then apply COUNT

SELECT
    gender as Gender,
    COUNT(*) as Total
FROM
(
    SELECT gender
    FROM memberOne
    UNION ALL
    SELECT gender
    FROM memberTwo
) group by gender

Upvotes: 2

Related Questions