Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (6.1k points)
edited by

Let's say I'm having 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

Will it be 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 can someone tell me about this ? 

1 Answer

0 votes
by (12.7k points)

For MySQL you can directly put the conditions in SUM() function and it will be evaluated as Boolean 0 or 1 and thus you will 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 company
GROUP BY company_name

If you want to learn more about SQL, Check out this SQL Certification by Intellipaat.

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
1 answer
asked Jul 11, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer

Browse Categories

...