Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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
like image 965
David Avatar asked Sep 10 '12 14:09

David


People also ask

How does GROUP BY work with multiple columns in SQL?

The GROUP BY clause is used along with some aggregate functions to group columns with the same values in different rows. The group by multiple columns technique retrieves grouped column values from one or more database tables by considering more than one column as grouping criteria.

Can we use COUNT and GROUP BY together?

The use of COUNT() function in conjunction with GROUP BY is useful for characterizing our data under various groupings. A combination of same values (on a column) will be treated as an individual group.

Can we use GROUP BY with 2 columns in SQL?

Yes, it is possible to use MySQL GROUP BY clause with multiple columns just as we can use MySQL DISTINCT clause.

Can we use COUNT on multiple columns in SQL?

You can use CASE statement to count two different columns in a single query. To understand the concept, let us first create a table. The query to create a table is as follows. Insert some records in the table using insert command.


1 Answers

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
like image 116
Fionnuala Avatar answered Sep 28 '22 18:09

Fionnuala