Practical Guide to Group, Join, and CASE WHEN Queries in SQL

This article records practical and frequently used SQL queries that the author once forgot, focusing on clear explanations without optimization, making it suitable for beginners.

1. Review of GROUP BY Queries

The group query groups data to calculate statistics per dimension, such as daily click counts for a specific public account. An example SQL statement is:

select name,time,sum(pv) as pv
from xxx_table
where name = 'Java3y'
group by name,time

Common misunderstandings include ordering before grouping, which can lead to incorrect results. The correct approach is to group first, then aggregate.

1.1 Common Misunderstanding

Some users order the data first and then group, which is not the intended usage of GROUP BY . Proper grouping should be performed before any aggregation.

1.2 Reason for Misunderstanding

Some tools allow SELECT * FROM xxx_table GROUP BY name , but after grouping only the grouped columns or aggregate functions may appear in the SELECT list.

1.3 Frequently Used GROUP BY SQL

To deduplicate records, you can select the minimum ID per group:

select * from user where id in (
    select min(id) from user where name = 'Java3y' and pv = 20 and time='7-25' group by name,pv,time
);

After grouping, you can use subqueries to retrieve additional fields not included in the GROUP BY clause.

2. Review of JOIN Queries

JOIN combines two tables when a single table cannot provide all needed information. The article explains inner join, left join, and right join, emphasizing the need for join conditions to avoid Cartesian products.

Examples of join types and their effects on result row counts are illustrated with diagrams.

3. Review of CASE WHEN

The CASE WHEN THEN ELSE END construct works like an if‑else statement in programming languages. Example syntax:

CASE WHEN sex = '1' THEN 'Male'
     WHEN sex = '2' THEN 'Female'
     ELSE 'Other' END

Multiple conditions can be combined, and aliases can be added after the END keyword.

4. Common Functions

4.1 Hive and Presto JSON Parsing

JSON can be stored in MySQL (supported from version 5.7). Hive uses get_json_object(param1,'$.param2') , while Presto provides json_array_get(xjson,index) and json_extract(xjson,'$.param2') .

4.2 Date Functions

Examples for querying yesterday, last 7 days, last 30 days, current month, and previous month using MySQL date functions are provided.

4.3 Other Useful Functions

Common functions such as length , concat , substring , count , max , min , sum , floor , and ceil are listed.

An additional example shows extracting the text inside brackets from titles using string functions:

select substring_index(left(title, instr(title, '】')-1), '【', -1)
FROM `xxx_table`;

The article concludes with a friendly sign‑off.