383

If I have a MySQL table looking something like this:

company_name    action  pagecount
-------------------------------
Company A       PRINT   3
Company A       PRINT   2
Company A       PRINT   3
Company B       EMAIL   
Company B       PRINT   2
Company B       PRINT   2
Company B       PRINT   1
Company A       PRINT   3

Is it possible to run a MySQL query to get output like this:

company_name    EMAIL   PRINT 1 pages   PRINT 2 pages   PRINT 3 pages
-------------------------------------------------------------
CompanyA        0       0               1               3
CompanyB        1       1               2               0

The idea is that pagecount can vary so the output column amount should reflect that, one column for each action/pagecount pair and then number of hits per company_name. I'm not sure if this is called a pivot table but someone suggested that?

Rick James
  • 135,179
  • 13
  • 127
  • 222
peku
  • 5,003
  • 3
  • 20
  • 15
  • 5
    It's called pivoting and it's much, much quicker to do this transformation outside of SQL. – N.B. Oct 06 '11 at 13:22
  • 1
    Excel rips through things like this, it's really difficult in MySQL as there is no "CROSSTAB" operator :( – Dave Rix Oct 06 '11 at 13:35
  • Yes it's currently done by hand in Excel and we are trying to automate it. – peku Oct 06 '11 at 13:59
  • @N.B. Is it advised to perform it in our application layer or it is just simpler to do it there? – giannis christofakis Sep 28 '15 at 13:17
  • 1
    @giannischristofakis - it really depends on what you and your coworkers deem simpler. Technology caught up quite a bit since I posted the comment (4 years) so it's totally up to what you feel is better - be it in application or SQL. For example, at my work we deal with similar problem but we're combining both SQL and in-app approach. Basically, I can't help you other than giving opinionated answer and that's not what you need :) – N.B. Sep 28 '15 at 13:46
  • A Stored Procedure to generate Pivot code (and run it): http://mysql.rjweb.org/doc.php/pivot – Rick James Jan 04 '20 at 18:52
  • The SQL language has a strict rule requiring you to know the number of types of columns at query compile time, before looking at any row data. If you have to look into the rows to know how many columns you need, you will not be able to do this in a single query. – Joel Coehoorn Dec 16 '22 at 22:52

11 Answers11

281

This basically is a pivot table.

A nice tutorial on how to achieve this can be found here: http://www.artfulsoftware.com/infotree/qrytip.php?id=78

I advise reading this post and adapt this solution to your needs.

Update

After the link above is currently not available any longer I feel obliged to provide some additional information for all of you searching for mysql pivot answers in here. It really had a vast amount of information, and I won't put everything from there in here (even more since I just don't want to copy their vast knowledge), but I'll give some advice on how to deal with pivot tables the sql way generally with the example from peku who asked the question in the first place.

Maybe the link comes back soon, I'll keep an eye out for it.

The spreadsheet way...

Many people just use a tool like MSExcel, OpenOffice or other spreadsheet-tools for this purpose. This is a valid solution, just copy the data over there and use the tools the GUI offer to solve this.

But... this wasn't the question, and it might even lead to some disadvantages, like how to get the data into the spreadsheet, problematic scaling and so on.

The SQL way...

Given his table looks something like this:

CREATE TABLE `test_pivot` (
  `pid` bigint(20) NOT NULL AUTO_INCREMENT,
  `company_name` varchar(32) DEFAULT NULL,
  `action` varchar(16) DEFAULT NULL,
  `pagecount` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`pid`)
) ENGINE=MyISAM;

Now look into his/her desired table:

company_name    EMAIL   PRINT 1 pages   PRINT 2 pages   PRINT 3 pages
-------------------------------------------------------------
CompanyA        0       0               1               3
CompanyB        1       1               2               0

The rows (EMAIL, PRINT x pages) resemble conditions. The main grouping is by company_name.

In order to set up the conditions this rather shouts for using the CASE-statement. In order to group by something, well, use ... GROUP BY.

The basic SQL providing this pivot can look something like this:

SELECT  P.`company_name`,
    COUNT(
        CASE 
            WHEN P.`action`='EMAIL' 
            THEN 1 
            ELSE NULL 
        END
    ) AS 'EMAIL',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '1' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 1 pages',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '2' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 2 pages',
    COUNT(
        CASE 
            WHEN P.`action`='PRINT' AND P.`pagecount` = '3' 
            THEN P.`pagecount` 
            ELSE NULL 
        END
    ) AS 'PRINT 3 pages'
FROM    test_pivot P
GROUP BY P.`company_name`;

This should provide the desired result very fast. The major downside for this approach, the more rows you want in your pivot table, the more conditions you need to define in your SQL statement.

This can be dealt with, too, therefore people tend to use prepared statements, routines, counters and such.

Some additional links about this topic:

Bjoern
  • 15,934
  • 4
  • 43
  • 48
  • 5
    the link seems to work for now... if it ever goes down again, try these : Google's cache http://webcache.googleusercontent.com/search?q=cache:Lj4F4ezFYuUJ:www.artfulsoftware.com/infotree/queries.php+&cd=1&hl=en&ct=clnk&gl=ca or the Internet Wayback Machine (http://web.archive.org/web/20070303120558*/http://www.artfulsoftware.com/infotree/queries.php) – Lykegenes Jun 20 '14 at 14:25
  • link is accessible at this url http://www.artfulsoftware.com/infotree/qrytip.php?id=78 – MrPandav Dec 29 '15 at 13:07
  • 1
    There is another way to generate a pivot table without using "if", "case", or "GROUP_CONCAT": https://en.wikibooks.org/wiki/MySQL/Pivot_table – user2513149 Oct 16 '16 at 16:15
  • You can remove the ELSE NULL from your CASE as hat is the default behavior (and conditional aggregation is wordy enough) – Caius Jard May 10 '20 at 06:51
105

My solution is in T-SQL without any pivots:

SELECT
    CompanyName,  
    SUM(CASE WHEN (action='EMAIL') THEN 1 ELSE 0 END) AS Email,
    SUM(CASE WHEN (action='PRINT' AND pagecount=1) THEN 1 ELSE 0 END) AS Print1Pages,
    SUM(CASE WHEN (action='PRINT' AND pagecount=2) THEN 1 ELSE 0 END) AS Print2Pages,
    SUM(CASE WHEN (action='PRINT' AND pagecount=3) THEN 1 ELSE 0 END) AS Print3Pages
FROM 
    Company
GROUP BY 
    CompanyName
shA.t
  • 16,580
  • 5
  • 54
  • 111
RRM
  • 3,371
  • 7
  • 25
  • 40
  • 2
    This works for me even on PostgreSQL. I prefer this method than using the crosstab extension on Postgres as this is *cleaner* – itsols Oct 10 '14 at 03:06
  • 7
    *"My solution is in T-SQL without any pivots:"* Not only SQL Server it should work on most database vendors which follows the ANSI SQL standards. Note that `SUM()` can only work with numeric data if you ned to pivot strings you will have to use `MAX()` – Raymond Nijland Mar 25 '19 at 16:25
  • 4
    I think the *CASE* is unnesesary in `SUM(CASE WHEN (action='PRINT' AND pagecount=1) THEN 1 ELSE 0 END)`, you can just do `SUM(action='PRINT' AND pagecount=1)` since the condition will be converted to `1` when true and `0` when false – kajacx Mar 02 '20 at 08:04
  • 2
    @kajacx yes, though it's needed on database that don't have that sort of Boolean manipulation. Given a choice between a "longer syntax that works on all dB" and a "shorter syntax that only works on ..." I would pick the former – Caius Jard May 10 '20 at 06:53
  • BE CAREFUL!!! This is THE BEST answer on that page: I've browsed Bjoern's answer and it was great, however nowadays you have to deal with full_group_by and so on so it does work in legacy mode ONLY! – Vilq Aug 02 '23 at 20:59
88

For MySQL you can directly put conditions in SUM() function and it will be evaluated as Boolean 0 or 1 and thus you can have your count based on your criteria without using IF/CASE statements

SELECT
    company_name,  
    SUM(action = 'EMAIL')AS Email,
    SUM(action = 'PRINT' AND pagecount = 1)AS Print1Pages,
    SUM(action = 'PRINT' AND pagecount = 2)AS Print2Pages,
    SUM(action = 'PRINT' AND pagecount = 3)AS Print3Pages
FROM t
GROUP BY company_name

DEMO

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
48

For dynamic pivot, use GROUP_CONCAT with CONCAT. The GROUP_CONCAT function concatenates strings from a group into one string with various options.

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN action = "',
      action,'"  AND ', 
           (CASE WHEN pagecount IS NOT NULL 
           THEN CONCAT("pagecount = ",pagecount) 
           ELSE pagecount IS NULL END),
      ' THEN 1 ELSE 0 end) AS ',
      action, IFNULL(pagecount,'')
      
    )
  )
INTO @sql
FROM
  t;

SET @sql = CONCAT('SELECT company_name, ', @sql, ' 
                  FROM t 
                   GROUP BY company_name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

DEMO HERE

General Grievance
  • 4,555
  • 31
  • 31
  • 45
Abhishek Gupta
  • 4,066
  • 24
  • 27
  • 4
    Pacerier, true man but for dynamic pivoting its one of the best approach – Abhishek Gupta Aug 19 '15 at 07:18
  • 3
    This works well if you have many values in the "actions" column or expect that list to grow over time, as writing a case statement for each value can be time consuming and hard to keep up to date. – Patrick Murphy Feb 02 '18 at 03:53
  • It returns the following error: "DDL and DML statements are not allowed in the query panel for MySQL; only SELECT statements are allowed. Put DDL and DML in the schema panel." – Daniele Jun 19 '23 at 14:41
32

A stardard-SQL version using boolean logic:

SELECT company_name
     , COUNT(action = 'EMAIL' OR NULL) AS "Email"
     , COUNT(action = 'PRINT' AND pagecount = 1 OR NULL) AS "Print 1 pages"
     , COUNT(action = 'PRINT' AND pagecount = 2 OR NULL) AS "Print 2 pages"
     , COUNT(action = 'PRINT' AND pagecount = 3 OR NULL) AS "Print 3 pages"
FROM   tbl
GROUP  BY company_name;

db<>fiddle here
Old sqlfiddle

How?

TRUE OR NULL yields TRUE.
FALSE OR NULL yields NULL.
NULL OR NULL yields NULL.
And COUNT only counts non-null values. Voilá.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    @Erwin, But how would you know that there are three columns? What if there's 5? 10? 20? – Pacerier Apr 02 '15 at 10:34
  • 2
    @Pacerier: The example in the question seems to suggest that. Either way, SQL *demands* to know the return type. a *completely* dynamic query is not possible. If the number of output columns can vary you need two steps: 1st build the query, 2nd: execute it. – Erwin Brandstetter Apr 02 '15 at 10:59
19

Correct answer is:

select table_record_id,
group_concat(if(value_name='note', value_text, NULL)) as note
,group_concat(if(value_name='hire_date', value_text, NULL)) as hire_date
,group_concat(if(value_name='termination_date', value_text, NULL)) as termination_date
,group_concat(if(value_name='department', value_text, NULL)) as department
,group_concat(if(value_name='reporting_to', value_text, NULL)) as reporting_to
,group_concat(if(value_name='shift_start_time', value_text, NULL)) as shift_start_time
,group_concat(if(value_name='shift_end_time', value_text, NULL)) as shift_end_time
from other_value
where table_name = 'employee'
and is_active = 'y'
and is_deleted = 'n'
GROUP BY table_record_id
Talha
  • 1,546
  • 17
  • 15
  • 1
    Is this just an example you had on hand? What is the structure of the `other_value` table? – Patrick Murphy Feb 02 '18 at 03:55
  • 2
    *"Correct answer is:"* Most likely not as it is missing the `SET` query to increase the defualt value which is limited to 1024 for GROUP_CONCAT after 1024 GROUP_CONCAT simply truncates the string without a error meaning unexpected results can happen.. – Raymond Nijland Jun 19 '19 at 11:11
  • sorry guys can't remember further details. I do stuff for fun and then forget or destroy the entire project. But when I stumble upon a challenge I share how I fixed it. I know my example is not very detailed but I guess it may give directions to those who know what they are up against :) – Talha Aug 30 '19 at 16:39
10

There is a tool called MySQL Pivot table generator, it can help you create a web-based pivot table that you can later export to excel(if you like). it can work if your data is in a single table or in several tables.

All you need to do is to specify the data source of the columns (it supports dynamic columns), rows, the values in the body of the table, and table relationship (if there are any) MySQL Pivot Table

The home page of this tool is https://mysqlreports.com/mysql-reporting-tools/mysql-pivot-table/

Peter Green
  • 137
  • 1
  • 4
4
select t3.name, sum(t3.prod_A) as Prod_A, sum(t3.prod_B) as Prod_B, sum(t3.prod_C) as    Prod_C, sum(t3.prod_D) as Prod_D, sum(t3.prod_E) as Prod_E  
from
(select t2.name as name, 
case when t2.prodid = 1 then t2.counts
else 0 end  prod_A, 

case when t2.prodid = 2 then t2.counts
else 0 end prod_B,

case when t2.prodid = 3 then t2.counts
else 0 end prod_C,

case when t2.prodid = 4 then t2.counts
else 0 end prod_D, 

case when t2.prodid = "5" then t2.counts
else 0 end prod_E

from 
(SELECT partners.name as name, sales.products_id as prodid, count(products.name) as counts
FROM test.sales left outer join test.partners on sales.partners_id = partners.id
left outer join test.products on sales.products_id = products.id 
where sales.partners_id = partners.id and sales.products_id = products.id group by partners.name, prodid) t2) t3

group by t3.name ;
irba
  • 41
  • 2
0

One option would be combining use of CASE..WHEN statement is redundant within an aggregation for MySQL Database, and considering the needed query generation dynamically along with getting proper column title for the result set as in the following code block :

SET @sql = NULL;

SELECT GROUP_CONCAT(
             CONCAT('SUM( `action` = ''', action, '''',pc0,' ) AS ',action,pc1)
       )
  INTO @sql
  FROM 
  ( 
   SELECT DISTINCT `action`, 
          IF(`pagecount` IS NULL,'',CONCAT('page',`pagecount`)) AS pc1,
          IF(`pagecount` IS NULL,'',CONCAT(' AND `pagecount` = ', pagecount, '')) AS pc0
     FROM `tab` 
    ORDER BY CONCAT(action,pc0) 
  ) t;

SET @sql = CONCAT('SELECT company_name,',@sql,' FROM `tab` GROUP BY company_name'); 
SELECT @sql; 

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Demo

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
0
SELECT company_name, SUM(CASE WHEN ACTION = 'Email' THEN 1 ELSE 0 END) AS "Email",
SUM(CASE WHEN ACTION = 'Print' AND pagecount = 1 THEN 1 ELSE 0 END) AS "print 1 PAGE",
SUM(CASE WHEN ACTION = 'Print' AND pagecount = 2 THEN 1 ELSE 0 END) AS "print 2 PAGE",
SUM(CASE WHEN ACTION = 'Print' AND pagecount = 3 THEN 1 ELSE 0 END) AS "print 2 PAGE"
FROM test1 GROUP BY company_name;
Slava Rozhnev
  • 9,510
  • 6
  • 23
  • 39
0

Use Query with Conditional Aggregation

Here's the query:

SELECT
    company_name,
    SUM(CASE WHEN action = 'EMAIL' THEN 1 ELSE 0 END) AS EMAIL,
    SUM(CASE WHEN action = 'PRINT' AND pagecount = 1 THEN 1 ELSE 0 END) AS `PRINT 1 pages`,
    SUM(CASE WHEN action = 'PRINT' AND pagecount = 2 THEN 1 ELSE 0 END) AS `PRINT 2 pages`,
    SUM(CASE WHEN action = 'PRINT' AND pagecount = 3 THEN 1 ELSE 0 END) AS `PRINT 3 pages`
FROM
    your_table_name
GROUP BY
    company_name;

Just replace your_table_name with the actual name of your MySQL table.

What will Query do?

his query will give you the desired output where each column represents the action/pagecount pair, and the number of hits per company_name is correctly counted.

Hope it helps.

Muhammad Ali
  • 956
  • 3
  • 15
  • 20