mysql group by, max, count implementaion

user_11363581 0
Viewed 166

tables

items table
------------------
| id |   item    |
------------------
| 1  |   pearl   |
| 2  |   ruby    |
| 3  |   gold    |
| 4  |   diamond |
------------------

transaction table
--------------------------------------------------
| item_id |   sell_price   |    created_at       |
--------------------------------------------------
|    1    |      10        | 2020-08-21 01:50:24 |
|    1    |      20        | 2020-08-22 05:51:10 |
|    3    |      20        | 2020-08-23 06:52:05 |
|    3    |      30        | 2020-08-24 22:55:07 |
|    4    |      50        | 2020-08-25 20:58:18 |
|    2    |      10        | 2020-08-26 12:59:55 |
--------------------------------------------------

expected output:

--------------------------------------------------------------------
| item_id |  name    |   quantity    | price |    created_at       |
--------------------------------------------------------------------
|    1    |  pearl   |      2        |  20   | 2020-08-22 05:51:10 |
|    2    |  ruby    |      1        |  10   | 2020-08-26 12:59:55 |
|    3    |  gold    |      2        |  30   | 2020-08-24 22:55:07 |
|    4    |  diamond |      1        |  50   | 2020-08-25 20:58:18 |
--------------------------------------------------------------------

the query

SELECT
    items.id AS item_id,
    items.item,
    COUNT(transaction.item_id) AS quantity,
    MAX(transaction.price) AS sell_price
FROM transaction
LEFT JOIN items
ON transaction.item_id = items.id 
GROUP BY transaction.item_id;

My query above works perfectly fine without transaction.created_at on select clause, but when I put select transaction.created_at it throws an error:

SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.transaction.created_at' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

select group-by mysql sql
2 Answers

From the desired output you shared, it looks like you're trying to query the maximum created_at:

SELECT
    items.id AS item_id,
    items.item,
    COUNT(transaction.item_id) AS quantity,
    MAX(transaction.price) AS sell_price,
    MAX(created_at) AS created_at -- Here!
FROM transaction
LEFT JOIN items
ON transaction.item_id = items.id 
GROUP BY transaction.item_id;
user_24227760

When using GROUP BY, just list all columns that are in SELECT and that not inside an aggregate function.

SELECT
    items.id AS item_id,
    items.item,
    COUNT(transaction.item_id) AS quantity,
    MAX(transaction.sell_price) AS sell_price,
    MAX(transaction.created_at) AS created_at
FROM transaction
LEFT JOIN items
ON transaction.item_id = items.id 
GROUP BY items.id, items.item;

See db-fiddle.

user_10521300
Related