Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Grouping data by month and year based on a count()

I have a table structure as follows:

Shops Table:

id shop_name
1 My Shop
2 Another Shop
3 Final Shop

Orders Table:

shop_id order_id data created_at
1 123 json 01/01/2023
2 456 json 01/01/2023
3 234 json 02/02/2023
1 765 json 03/02/2023
2 435 json 04/03/2023
3 788 json 04/03/2023

From these 2 tables, I would like to select the shops name, ID and the count of its orders grouped by the month they were created, something like:

shop_id shop_name order_count_january_23 order_count_february_23 etc
1 My Shop 1 2 …
2 Another Shop 12 45 …
3 Final Shop 8 36 …

I have tried grouping the number of orders but i can’t seem to be able to get the exact output I need, I’ve also tried using sub-queries but to no avail.

SELECT orders.id,
       shops.name,
       count(orders.id) as 'Orders',
       MONTH(orders.created_at) as Month,
       YEAR(orders.created_at) as Year
FROM orders
JOIN shops on shops.id = orders.id
GROUP BY orders.wholesaler_id, Year, Month

I know the above does not work however this is where I became stumped. It does however, give me a list of shops with there order numbers but this is not in the correct format

Any help is massivley appreciated.

Tags:

2

Answers


  1. 0

    FOR MYSQL

    SET SESSION group_concat_max_len = 1000000;

SET @columns = NULL;

SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        'SUM(CASE WHEN MONTH = ', MONTH, 
        ' AND YEAR = ', YEAR, 
        ' THEN 1 ELSE 0 END) AS order_count_', 
        MONTHNAME, '_', YEAR
    )
    SEPARATOR ', '
) INTO @columns
FROM (
    SELECT DISTINCT MONTH(created_at) AS MONTH, YEAR(created_at) AS YEAR, 
           MONTHNAME(created_at) AS MONTHNAME
    FROM Orders
) AS months;

SET @sql = CONCAT('SELECT 
                    s.ID AS shop_id,
                    s.shop_name,
                    ', @columns, '
                  FROM
                    shops s
                  LEFT JOIN (
                      SELECT shop_id, 
                             MONTH(created_at) AS MONTH, 
                             YEAR(created_at) AS YEAR
                      FROM orders
                      GROUP BY shop_id, MONTH(created_at), YEAR(created_at)
                  ) o ON s.ID = o.shop_id
                  GROUP BY
                    s.ID, s.shop_name');

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

    FOR MSSQL

    DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- Generate a comma-separated list of aggregated expressions for each month and year
SELECT @columns = COALESCE(@columns + ', ', '') +
                 'SUM(CASE WHEN MONTH = ' + CONVERT(NVARCHAR(2), MONTH) + 
                 ' AND YEAR = ' + CONVERT(NVARCHAR(4), YEAR) + 
                 ' THEN 1 ELSE 0 END) AS order_count_' + MONTHNAME + '_' + CONVERT(NVARCHAR(4), YEAR)
FROM (
  SELECT DISTINCt MONTH(created_at) AS MONTH, YEAR(created_at) AS YEAR, FORMAT(created_at, 'MMMM') AS MONTHNAME
  FROM Orders
) AS months;

-- Construct the dynamic SQL query
SET @sql = CONCAT('SELECT 
                    s.id AS shop_id,
                    s.shop_name,
                    ', @columns, '
                  FROM
                    Shops s
                  LEFT JOIN (
                      SELECT shop_id, 
                             MONTH(created_at) AS MONTH, 
                             YEAR(created_at) AS YEAR
                      FROM Orders
                      GROUP BY shop_id, MONTH(created_at), YEAR(created_at)
                  ) o ON s.id = o.shop_id
                  GROUP BY
                    s.id, s.shop_name');

-- Execute the dynamic SQL query
EXEC sp_executesql @sql;
    Login or Signup to reply.
  2. 0

    Try using COUNT with IF, like so:

    SELECT
       orders.id,
       shops.name,
       COUNT(IF(
              MONTH(orders.created_at) = 1 AND YEAR(orders.created_at) = 2023,
              1,
              NULL
       )) AS order_count_january_23,
FROM orders
JOIN shops on shops.id = orders.id
GROUP BY orders.wholesaler_id
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search