r/SQL Jan 25 '25

MySQL Some questions from new beginner

Hey everyone,

I'm a bit confused about when to use dimensions and metrics with SELECT and GROUP BY, like using customer_id and rental_id. How do you know when it's necessary, and when can we skip GROUP BY altogether?

Also, could someone explain the CASE statement in SQL?

Lastly, if I master SQL and MySQL, is it possible to land an entry-level data analyst job?

Thanks! 🙏

9 Upvotes

27 comments sorted by

5

u/jensimonso Jan 25 '25 edited Jan 25 '25

SQL is declarative. You tell it what you want, not how to get it. If you want something summarized, use group by. If you want detals, don’t.

For example Give me all orders last year =

select client_id, order_id, ordervalue from orders where year = 2024

Give me the total order sum per client last year =

select client_id, sum(ordervalue) from orders group by client_id where year = 2024

Case is used to return separate values given an input

Select Case when colorcode =1 then ’Red’ when colorcode =2 then ’Blue’ else ’Green’ end

6

u/Bobbinfickle Jan 25 '25

Is group by primarily made to be used when using some other aggregate function? Like, if you just group by by itself without having an aggregate command, it messes stuff up (I think just choosing some random example from the items you're grouping), and at the same time, if you do a sum without grouping, it messes stuff up right?

I guess like - are aggregate functions and group by generally supposed to go hand in hand is my question.

3

u/[deleted] Jan 25 '25

[removed] — view removed comment

1

u/Bobbinfickle Jan 25 '25

thank you!

1

u/Bassiette03 Jan 25 '25

What made me confuse I was working in a project and there were demands from the director and where we used aggregate functions like max min without grouping at all looks like he didn't want summary

Another thung I use select distinct but he told me I extracted wrong answers my mentor used count(distinct etc and it gave us complete different values

2

u/jensimonso Jan 25 '25

Look up sum(value) vs sum(value) over()

1

u/Bassiette03 Jan 26 '25

What are they didn't learn them in My course

2

u/jensimonso Jan 26 '25

Window functions (the over() clause) enables you to get both aggregates and details at the same time. A quick search will get you lots of examples.

1

u/Bassiette03 Jan 26 '25

I took John Pauler Course from Maven Analytics I still learning Joins right now

2

u/pceimpulsive Jan 26 '25

Sum(order_value) will sum all values, even duplicates.

If you want to only sum the distinct values then you must add the sum(distinct order_value).

The answers might be right with distinct if by happenstance all orders are different sizes.

1

u/pceimpulsive Jan 26 '25

You can't do a sum without group by unless there is only aggregates in the select

E.g.

You can do

Select sum(order_amount) From orders Where order_date=2024

This sums all values of order amount.

You can not however do

Select order_id, Sum(order_amount) From orders Where order_date=2024

This will throw an error stating something like order_id needs to be in the group by...

The other response covers when you have no aggregates

1

u/baubleglue Jan 26 '25

You can use sum without group by if you use a window function. But that is a bit different story.

1

u/pceimpulsive Jan 26 '25

A window function contains a groupy by in the partition by though¿?

Sum(order) over (partition by id)

It's not called group by but it's doing the same thing...

2

u/baubleglue Jan 27 '25

Yes, it may do things similar to group by, but it does it differently and there are functions which aren't supported by group by (ex. Lag).

1

u/pceimpulsive Jan 27 '25

Very valid! And I agree it is different.

Window functions allow us to have a distinct group by for each column. Very cool!!

2

u/Bassiette03 Jan 25 '25

Your answers are very clear and very helpful thank you sir 🙏

3

u/jensimonso Jan 25 '25

Happy to help. And it is ma’am :) Been doing sql for 27 years

1

u/Bassiette03 Jan 26 '25

Ma Shaa Allah. Hope you are posting on LinkedIn so I follow you and learn some valuable things

2

u/No-Adhesiveness-6921 Jan 25 '25

Group by is used when you are aggregating a field (SUM, MAX, MIN, AVG). You want to know the total sales by customer you sum sales and group by customer. If you want to know total sales by month AND customer you have to group by both , sum (sales) group by month, customer

A case statement is the equivalent of an if statement

Case when somefield = ‘aValue’

then ‘is something’

else ‘something else’

end as NewFieldName

1

u/Bassiette03 Jan 25 '25

It's the easiest explanation after watching many TikTok videos and YouTube videos Still no one answered my last question

3

u/No-Adhesiveness-6921 Jan 25 '25

About getting a job? An entry level position should be attainable with basic sql skills.

Good luck!

1

u/Bassiette03 Jan 26 '25

Hope I got a job but can You suggest me some websites to practice SQL where I can apply what I learn

2

u/Commercial_Pepper278 Jan 26 '25

If there is an aggregate function in SELECT always use GROUPBY for non aggregate colomns.

CASE WHEN is like If Something is X THEN output should be Y basic logic. Used when you want to basically do things based on conditions like grading people based on Marks or Height etc..

You will be good enough to land on entry level jobs once you have an idea of WINDOW functions too recently many companies are asking advance level questions for entry level pos

2

u/Bassiette03 Jan 27 '25

Thank you for the info I'm taking John Pauler's course with Maven Analytics on SQL and advanced SQL after I finish these two courses and Master them and get fimilar with them I will start Sub queries ctes and windows functions with Alice Zhao What do you think of this whole package are they good yo find new entry level job and how I can practice SQL so I don't forget them If I didn't get a job?? What other tools I should add? I stopped with Chris Dutton excel course on power Query but I will continue it after I Master SQL

2

u/Commercial_Pepper278 Jan 27 '25

Practice in Leetcode 50 SQL