SQL

Get subtotals with ROLLUP+GROUP BY

In: SQL

π™π™Šπ™‡π™‡π™π™‹ generates extra rows that represent the subtotals.

For example…

To get the sum of spending of each user, you typically GROUP BY user_id and SUM spend.

Want to get the sum of ALL users in the same result? Try ROLLUP.

πŸ“Œ The syntax is here.
πš‚π™΄π™»π™΄π™²πšƒ
πš„πšœπšŽπš›_πš’πš, πš‚πš„π™Ό(πšœπš™πšŽπš—πš)
π™΅πšπ™Ύπ™Ό
πš„πšœπšŽπš›πšœ
π™Άπšπ™Ύπš„π™Ώ π™±πšˆ πšπ™Ύπ™»π™»πš„π™Ώ(πšžπšœπšŽπš›_πš’πš)

The result will contain a NULL user_id which contains the sum of spend of all users.

πŸš€ In the demo below, we do a roll-up of two aggregation columns, and show the use of a partial roll-up. Check it out.

Bonus point if you can spot the mistake in the demo below.

More from Travis Tang
Use ARRAY_AGG to flatten columns to lists.
SQL

Use ARRAY_AGG to flatten columns to lists.

You need to convert a long table of values into a list? This is the most convenient function. πŸ“Œ Syntax: πš‚π™΄π™»π™΄π™²πšƒ π™°πšπšπ™°πšˆ_𝙰𝙢𝙢(πšŒπš˜πš•πšžπš–πš—) π™΅πšπ™Ύπ™Ό πšπšŠπš‹πš•πšŽ There
Pivot Table (From Long to Wide)
SQL

Pivot Table (From Long to Wide)

Every data scientist, scientist, and engineer should know how to create a pivot table. π™²π™°πš‚π™΄ πš†π™·π™΄π™½ is the best way to do
Great! You’ve successfully signed up.
Welcome back! You've successfully signed in.
You've successfully subscribed to Travis Tang.
Your link has expired.
Success! Check your email for magic link to sign-in.
Success! Your billing info has been updated.
Your billing was not updated.