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?
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;
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 ;
There is a tool called MySQL Pivot table generator, it can help you create 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)
The home page of this tool is http://mysqlpivottable.net
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
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
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
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;
How?
TRUE OR NULL
yields TRUE
.
FALSE OR NULL
yields NULL
.
NULL OR NULL
yields NULL
.
And COUNT
only counts non-null values. Voilá.
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;
Source: Stackoverflow.com