CODEWITHSUNDEEP
sqlms-accesscountgroup-by
David
David

Reputation: 75

SQL, count in multiple columns then group by

I am trying to count in multiple columns then group by for a total sum where the same data appears in any column

Source data table:

P1  P2  P3
-----------
a   b   
a   a   a
b   c   
a   b   b
b   a

I want it to show something like this:

Desired query output:

     Total
   -------------
a |    6
b |    5
c |    1

Upvotes: 6

Views: 5159

Answers (2)

John Woo
John Woo

Reputation: 263693

You can union all the records in a subquery and on the outer query, count each value.

SELECT b.a, COUNT(b.a)
FROM
    (
        SELECT P1 a
        FROM tableName
        UNION ALL
        SELECT P2 a
        FROM tableName
        UNION ALL
        SELECT P3 a
        FROM tableName
    ) b
GROUP BY b.a

Upvotes: 5

Fionnuala
Fionnuala

Reputation: 91316

You can use a union query

SELECT x.f1,Count(x.f1) FROM
(SELECT p1 As F1 FROM table
 UNION ALL
 SELECT p2 As F1 FROM table
 UNION ALL
 SELECT p3 As F1 FROM table) x
GROUP BY x.f1

Upvotes: 7

Related Questions