MySQL: GROUP BY

#MySQL #tutorial #course

You can copy and paste all of the following statements if you would like to follow along in this video.
P.S. Make sure you have a customers table if you're linking the foreign key on customer_id.
------------------------------------------------------------
DROP TABLE IF EXISTS transactions;

CREATE TABLE transactions (
transaction_id INT PRIMARY KEY AUTO_INCREMENT,
amount DECIMAL(5, 2),
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);

INSERT INTO transactions
VALUES (1000, 4.99, 3, "2023-01-01"),
(1001, 2.89, 2, "2023-01-01"),
(1002, 3.38, 3, "2023-01-02"),
(1003, 4.99, 1, "2023-01-02"),
(1004, 1.00, NULL, "2023-01-03"),
(1005, 2.49, 4, "2023-01-03"),
(1006, 5.48, NULL, "2023-01-03");

SELECT * FROM transactions;
------------------------------------------------------------

00:00:00 intro
00:00:16 data for this demo
00:00:00 GROUP BY
00:03:19 HAVING
00:04:36 conclusion

[BroCodez]

You can copy and paste all of the following statements if you would like to follow along in this video.
P.S. Make sure you have a customers table if you're linking the foreign key on customer_id.

DROP TABLE IF EXISTS transactions;

CREATE TABLE transactions (
transaction_id INT PRIMARY KEY AUTO_INCREMENT,
amount DECIMAL(5, 2),
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);

INSERT INTO transactions
VALUES (1000, 4.99, 3, "2023-01-01"),
(1001, 2.89, 2, "2023-01-01"),
(1002, 3.38, 3, "2023-01-02"),
(1003, 4.99, 1, "2023-01-02"),
(1004, 1.00, NULL, "2023-01-03"),
(1005, 2.49, 4, "2023-01-03"),
(1006, 5.48, NULL, "2023-01-03");

SELECT * FROM transactions;


-- EXAMPLE 1 --
SELECT SUM(amount), order_date
FROM transactions
GROUP BY order_date;

-- EXAMPLE 2 --
SELECT COUNT(amount), customer_id
FROM transactions
GROUP BY customer_id
HAVING COUNT(amount) > 1 AND customer_id IS NOT NULL;

[youneverknew_me]

I've read the article about GROUP BY but still don't understand. Your explanation is simply the best!

[zabehullahalizadeh]

I love your voice and the way that you teach us. Thank you Bro Code

[lordofdeath]

bro you are a treasure, thank you for existing

[pravinv_]

we can use where Clause also.
Give the Where clause above the GroupBy Clause.But for Aggregate functions we need to use Having within Groupby

[APDesignFXP]

Dude couldn’t have a better timing i just got my assignment in sql

[maxlin]

FINALLY an explanation that i understand. Thanks!

[abundance]

Thanks a lot for explaining it so nicely

[Blizz-rmec]

thank you i have an asessment tomorrow it helped a lot

[ned]

Your videos have really helped me. I really appreciate it!

[YoshiGG_]

thank you brocode Another day Another Victory for the ogs

[hackerexploit]

Please make a bash & powershell series too 😊

[shawnchristophermalig]

Can you make a video, explaining the math or innerworkings of GROUP by? I think what others seemed to get confused about group by is the fact that the math isn't mathin'.

I mean that you visualize select, but it's hard to visualize into thinking the group by. :)

Questions like: is it correct that 2 invisible became a pattern marker for group by? Something like that! 😁

[vaibhavsinhbihola]

Hey I am also using MySQL workbench mine text editor is not same as you how I can changed it to dark and like you

[itz_phil]

Are you able to do more videos about javaFX?

[shortsShowcase]

Can we use DISTINCT instead of GROUP BY

[fredianriko]

could you please share the sql dump so we could also practice with the exact data you have?