Mysql: Pivot + Counting

MySQL: Pivot + Counting

Use:

  SELECT t.fk,
         SUM(CASE WHEN t.status = 100 THEN 1 ELSE 0 END) AS count_100,
         SUM(CASE WHEN t.status = 101 THEN 1 ELSE 0 END) AS count_101,
         SUM(CASE WHEN t.status = 102 THEN 1 ELSE 0 END) AS count_102
    FROM TABLE t
GROUP BY t.fk

MySql Pivot Table with Count

We can do a two step aggregation. First, aggregate by customer and month to get the various counts per customer in each month. Then aggregate by month and pivot to get the final result.

SELECT month_year,
       SUM(total = 1) AS one_order,
       SUM(total = 2) AS two_order,
       SUM(total = 3) AS three_order,
       SUM(total = 4) AS four_order,
FROM
(
    SELECT DATE_FORMAT(order_date, '%b %y') AS month_year, customer_id,
           SUM(fulfilled) AS total
    FROM orders
    GROUP BY 1, 2
) t
GROUP BY month_year;

How to count values ​on a pivot table in MySQL?

You want conditional aggregation here:

SELECT
    u.id_user,
    COUNT(CASE WHEN c.status = 'ABAN'  THEN 1 END) AS ABAN,
    COUNT(CASE WHEN c.status = 'DES'   THEN 1 END) AS DES,
    COUNT(CASE WHEN c.status = 'LET'   THEN 1 END) AS LET,
    COUNT(CASE WHEN c.status = 'NOANS' THEN 1 END) AS NOANS,
    COUNT(CASE WHEN c.status NOT IN ('ABAN', 'DES', 'LET', 'NOAN')
               THEN 1 END) AS OTH
FROM Users u
LEFT JOIN Get_cal c
    ON c.id_user = u.id_user
GROUP BY
    u.id_user
ORDER BY
    u.id_user;

Mysql count of values on pivoting or transposing

select location as city,
       sum(dept = 'eee') as eee,
       sum(dept = 'ece') as ece,
       sum(dept = 'mech') as mech
from your_table
group by location

MySQL pivot table counting

Try this

SELECT u.name, GROUP_CONCAT(s.name), COUNT(s.subject_id) as Subject_count
FROM users u
LEFT JOIN  pivot_user_subject us ON u.user_id = us.user_id
LEFT JOIN subjects s ON s.subject_id = us.subject_id
GROUP BY u.user_id, u.name

SQL Fiddle

Mysql pivot table with non-count

I would use group_concat function:

SELECT c.id, c.name, c.email, w.day_of_week,
    group_concat(CASE WHEN w.type_of_food = 'Breakfast' THEN w.name_of_food END ) As breakfests,
    group_concat(CASE WHEN w.type_of_food = 'Snack' THEN w.name_of_food END ) As Snacks,
    group_concat(CASE WHEN w.type_of_food = 'Lunch' THEN w.name_of_food END ) As Lunches,
    group_concat(CASE WHEN w.type_of_food = 'Dinner' THEN w.name_of_food END ) As Dinners
FROM customer c
JOIN weekly_report w ON c.id = w.customerid
GROUP BY c.id, c.name, c.email, w.day_of_week

Demo: http://sqlfiddle.com/#!9/362f1cd/3

| id | name |            email | day_of_week | breakfests |           Snacks | Lunches | Dinners |
|----|------|------------------|-------------|------------|------------------|---------|---------|
|  1 | acc1 | acc1@yopmail.com |         Mon |       Eggs | Fries,Apple,Curd |    Rice |   Bread |
|  1 | acc1 | acc1@yopmail.com |         Tue |      Bread |           (null) |  (null) |    Milk |
|  2 | acc2 | acc2@yopmail.com |         Mon |       Eggs |      Fries,Apple |    Rice |   Bread |
|  2 | acc2 | acc2@yopmail.com |         Tue |     (null) |             Curd |   Bread |    Milk |

MySql Pivot table based on Count of Transactions

You need something like this:

SELECT Organization, MemberNumber, ItemID,
       SUM(CASE WHEN QuantityItemsPurchased = 1 
           THEN QuantityOfTransactions ELSE 0 END) As `1`,
       SUM(CASE WHEN QuantityItemsPurchased = 2 
           THEN QuantityOfTransactions ELSE 0 END) As `2`,
       SUM(CASE WHEN QuantityItemsPurchased = 3 
           THEN QuantityOfTransactions ELSE 0 END) As `3`,
       SUM(CASE WHEN QuantityItemsPurchased = 4 
           THEN QuantityOfTransactions ELSE 0 END) As `4`,
       SUM(CASE WHEN QuantityItemsPurchased = 5 
           THEN QuantityOfTransactions ELSE 0 END) As `5`,
       SUM(CASE WHEN QuantityItemsPurchased >= 6 
           THEN QuantityOfTransactions ELSE 0 END) As `6+`
FROM Table1
GROUP BY Organization, MemberNumber, ItemID

Demo: SQL Fiddle

MySQL count all records that have at least one record on a pivot table

When you make left join, you create intersection, that is larger than attendants table. Your join consists of rows with repeating attendant_id and different event uuid.

You can watch the intersection by executing SELECT IF(uuid <=> NULL, 'NO', 'YES') as status, att.id, ae.uuid FROM attendants att LEFT JOIN attendant_event ae ON ae.attendant_id = att.id. It includes 7 rows, 6 of them are with YES events for two active attendats and 1 row with NO events.

So you should count only distinct values:

SELECT IF(uuid <=> NULL, 'NO', 'YES') as status, count(distinct(att.id)) as count 
   FROM attendants att 
   LEFT JOIN attendant_event ae ON ae.attendant_id = att.id 
   GROUP BY status

Count in pivot table based on Date period

remove the else part as you are using count so when you put else 0 it is counting it, if you use sum then it is ok but for count you can skip the else so it consider other value as null and give you exact count

SELECT bkg.status,
        count(CASE WHEN bkg.StartDate between '2018-09-17' and '2018-10-17' THEN bkg.status END) AS 'sep',
        count(CASE WHEN bkg.StartDate between '2018-10-17' and '2018-11-17' THEN bkg.status END) AS 'Oct'
    FROM tbl_booking bkg 
GROUP BY bkg.status

How to create a count of values in mysql grouped by date?

You can use below query:

SELECT 
    COUNT(`id`) AS counts,
    SUM(CASE WHEN infected = '1' THEN 1 ELSE 0 END) AS infected,
    SUM(CASE WHEN infected = '0' THEN 1 ELSE 0 END) AS not_infected, 
    SUM(CASE WHEN infected IS NULL THEN 1 ELSE 0 END) AS not_tested,
    SUM(CASE WHEN absence = '1' THEN 1 ELSE 0 END) AS absence,
    SUM(CASE WHEN absence = '0' THEN 1 ELSE 0 END) AS not_absence,
    DATE(`date_checkin`) AS selected_day
FROM 
    users
GROUP BY 
    selected_day;

---

Related Topics