Get COUNT/SUM of records based on another column in SQL - Mysql

jack
December 12, 2022
158 views
4 votes
4 Answers

How do I get the count/sum of the rows (COUNT () or SUM ()) based on another column (of the Type: weekly or yearly)? I have two tables:

Stores:

Id	Name	Type
1	Store 1	Weekly
2	Store 2	Yearly
3	Store 3	Weekly
4	Store 4	Weekly

Orders:

Id	StoreId	OrderDate	Qty
1	1	2022-01-31	2
2	1	2022-12-31	5*
3	2	2022-01-28	30*
4	2	2022-06-30	50*
5	2	2022-12-31	70*
6	3	2022-06-15	8
7	3	2022-12-27	9*
8	3	2022-12-31	3*

a) If I pass the date range (by weekly,2022-12-26 ~ 2023-01-01), the expected result should look like this:

Id	Name	Count of orders	Total Qty
1	Store 1	1	5
2	Store 2	3	150 (sum by the year when the store’s type equals "Yearly": 30+50+70)
3	Store 3	2	12 (sum by the selected week: 9+3)
4	Store 4	0	0

If the Store type is Yearly then all orders will be summed up based on StoreId & year of OrderDate, if Weekly then based on StoreId & selected OrderDate.

b) I tried using CASE in SELECT statement, but no luck, here are part of my codes:

SELECT s.Id,
       s.Name,
       COUNT(o.Id) AS 'Count of orders',
       sum(o.Qty) AS 'Total Qty'
  FROM Stores AS s
  LEFT JOIN Orders AS o
    ON o.StoreId = s.id
   AND (OrderDate >= '2022-12-26' AND OrderDate <= '2023-01-01')
 GROUP BY s.Id, OrderDate
 ORDER BY OrderDate DESC

Tags: mysql sql

Answers

You could use conditional aggregation as the following:

SELECT s.Id,
       s.Name,
       COUNT(CASE
               WHEN s.Type = 'Yearly' THEN
                o.Id
               ELSE
                CASE
                  WHEN OrderDate >= '2022-12-26' AND OrderDate <= '2023-01-01' THEN
                   o.Id
                END
             END) As 'Count of orders',
       SUM(CASE
             WHEN s.Type = 'Yearly' THEN
              o.Qty
             ELSE
              CASE
                WHEN OrderDate >= '2022-12-26' AND OrderDate <= '2023-01-01' THEN
                 o.Qty
                ELSE
                 0
              END
           END) AS 'Total Qty'
  FROM Stores AS s
  LEFT JOIN Orders AS o
    ON o.StoreId = s.id
 GROUP BY s.Id, s.Name
 ORDER BY MAX(OrderDate) DESC

See demo.

- learning
- December 12, 2022 at 7:03 am
- 0 votes
0
You can do in this way.
Please take note that, type is a keyword in MySQL.
```
SELECT s.id,
       s.name,
       s.type,
       COUNT(s.name) AS total_count,
       SUM(o.qty) AS total_qty
  FROM stores s
  LEFT JOIN orders o
    ON s.id = o.storeid
 WHERE (o.orderdate >= '2022-12-26' AND o.orderDate <= '2023-01-01' 
   AND  s.type = 'Weekly')
    OR  s.type = 'Yearly'
 GROUP BY s.id, s.name, s.type
```
Login or Signup to reply.

From the description, calculate count(Orders.Id) and sum(Orders.Qty)

Stores.Type = ‘Weekly’: Orders.OrderDate between @start_date and @end_date
Stores.Type = ‘Yearly’: Orders.OrderDate in the year of @start_date (…all orders will be summed up based on StoreId & year of OrderDate.)

Thus, the first step is to have where clause to filter out Orders and then aggregate to Store.Id level. Then, 2nd step is to left join from Stores table to the result of first step so that stores without sales in specified date ranges are reported.

set @start_date = '2022-12-26', @end_date = '2023-01-01';

with cte_store_sales as (
select s.Id,
       count(o.Id) as order_count,
       sum(o.Qty)  as total_qty
  from stores s
  left 
  join orders o
    on s.Id = o.StoreId
 where (s.type = 'Weekly' and o.OrderDate between @start_date and @end_date)
    or (s.type = 'Yearly' and o.OrderDate between makedate(year(@start_date),1)
                                              and date_sub(date_add(makedate(year(@start_date),1), interval 1 year), interval 1 day))
 group by s.Id)
select s.Id,
       s.Name,
       coalesce(ss.order_count, 0) as "Count of Orders",
       coalesce(ss.total_qty, 0)   as "Total Qty"
  from stores s
  left
  join cte_store_sales ss
    on s.Id = ss.Id
 order by s.Id;

Output:

Id|Name   |Count of Orders|Total Qty|
--+-------+---------------+---------+
 1|Store 1|              1|        5|
 2|Store 2|              3|      150| <-- Store sales in year 2022
 3|Store 3|              2|       12|
 4|Store 4|              0|        0| <-- Report stores without sales

First of all, we shall extract the raw data matching the orderdate table condition, which can be used for the sake of aggregation later. Note,here I treat the date range as inclusive. Therefore, it shall be year 2022 and 2023 for 2022-12-26 ~ 2023-01-01 if the type is yearly.

    select s.id id, name,
        (case when type='weekly' and orderdate between '2022-12-26' and '2023-01-01' then qty
         when type='yearly' and year(orderdate) between year('2022-12-26') and year('2023-01-01') then qty
        end) as qt
    from Stores s
    left join Orders o
    on s.id=o.storeid;
-- result set:
# id, name, qt
1, Store 1, 5
2, Store 2, 30
2, Store 2, 50
2, Store 2, 70
3, Store 3, 
3, Store 3, 9
3, Store 3, 3
4, Store 4,

The rest is to do the summarisation job using the derived table. Note: Since the column name is not in the group by list, but it’s actually unique for a specific storeid, we can use the any_value function to bypass the restriction which might be enforced due to the SQL_MODE system variable.

select id,any_value(name) as'Name',count(qt) as 'Count of orders', ifnull(sum(qt),0) as 'Total Qty'
from
    (select s.id id, name,
        (case when type='weekly' and orderdate between '2022-12-26' and '2023-01-01' then qty
         when type='yearly' and year(orderdate) between year('2022-12-26') and year('2023-01-01') then qty
        end) as qt
    from Stores s
    left join Orders o
    on s.id=o.storeid) tb
group by id
order by id
;
-- result set:
# id, Name, Count of orders, Total Qty
1, Store 1, 1, 5
2, Store 2, 3, 150
3, Store 3, 2, 12
4, Store 4, 0, 0

Please signup or login to give your own answer.

Click here to cancel reply.

Get COUNT/SUM of records based on another column in SQL – Mysql

Answers