Mysql GROUP BY two columns

sql

subqueries

group-by

having-clause

byAnton Shumikhin·Aug 24, 2024

Quickly group results by unique pairs using GROUP BY column1, column2 like:

SELECT column1, column2, COUNT(*) AS Count
FROM your_table
GROUP BY column1, column2;

This groups column1 and column2 into distinct tuples and counts each occurrence through COUNT(*).

Leveling up with subqueries

To squeeze the max value out of your data, subqueries will be your new best friend:

/* We've all wished to maximize our portfolio value, right?! */
SELECT c.client_id, p.portfolio_id, 
       (SELECT MAX(value) FROM portfolios WHERE client_id = c.client_id) AS max_value
FROM clients c
JOIN portfolios p ON p.client_id = c.client_id
GROUP BY c.client_id, p.portfolio_id;

Here, a subquery fetches the maximum portfolio value for each client.

Join mastery in GROUP BY

Don't just JOIN tables, dominate them:

/* Money making magic starts here */
SELECT c.client_id, p.portfolio_id, SUM(p.value) AS total_value
FROM clients c
JOIN portfolios p ON c.client_id = p.client_id
GROUP BY c.client_id, p.portfolio_id
/* Who doesn't like their money>10K? */
HAVING total_value > 10000;

Utilizing HAVING for the upper hand

The HAVING clause allows you to filter results after aggregation, kind of like the postseason in sports:

/* We're filtering for big fishes here, no small fry allowed */
SELECT c.client_id, p.portfolio_id, SUM(p.value) AS total_value
FROM clients c
JOIN portfolios p ON c.client_id = p.client_id
GROUP BY c.client_id, p.portfolio_id
HAVING SUM(p.value) > 10000;

Here, the stare goes to the big fish portfolios with a total value exceeding 10,000. The underdogs don't stand a chance.

Grouping the ungroupable with CONCAT

Sometimes you gotta MAKE a way. Use CONCAT for a unique identifier from multiple columns:

/* Let's play with a little identity crisis here */
SELECT CONCAT(client_id, '_', portfolio_id) AS grouped_id, SUM(value) as total_value
FROM portfolios
GROUP BY grouped_id;

COLUMN inclusion proficiency

If a column shows up in the SELECT statement but not in a boy band named GROUP BY, it's gotta be part of an aggregate function:

SELECT client_id, portfolio_id, SUM(value) 
FROM portfolios
GROUP BY client_id, portfolio_id;

Serve your data in style

When it comes to serving data, the order of the day is, well, ORDER BY. And LIMIT is like a VIP pass for your data:

/* Watch these portfolios strut their wealth down the runway */
SELECT client_id, portfolio_id, SUM(value) AS total_value
FROM portfolios
GROUP BY client_id, portfolio_id
ORDER BY total_value DESC
/* Top 30 portfolios, because too much wealth can be a bad thing, right?! */
LIMIT 30;

Filter like a boss

WHERE clause gives you the power to place entry barriers better than a high-end nightclub:

/* The VIP area only for active clients */
SELECT client_id, portfolio_id, SUM(value) AS total_value
FROM portfolios
WHERE client_id IN (SELECT client_id FROM clients WHERE active = 1)
GROUP BY client_id, portfolio_id;

Ensuring uniqueness in results

Preserving uniqueness in results is like the ability to tell twins apart:

Group by client and portfolio ID.
Use distinct if necessary.
Double-check your JOIN conditions.

Advanced Grouping Techniques

Dynamic GROUP BY with CASE WHEN

Add some spice to your GROUP BY with CASE WHEN:

/* Let's get these portfolios into high and low value brackets */
SELECT 
  CASE WHEN value > 10000 THEN 'High' ELSE 'Low' END AS ValueBracket, 
  COUNT(*) AS TotalPortfolios
FROM portfolios
GROUP BY ValueBracket;

Doing a Matrix Style cross-tabulation

Turn your data into a pivot table Matrix-style:

/* Matrix Reloaded: The SQL version */
SELECT 
  client_id,
  SUM(CASE WHEN portfolio_id = 'A' THEN value ELSE 0 END) AS PortfolioA,
  SUM(CASE WHEN portfolio_id = 'B' THEN value ELSE 0 END) AS PortfolioB
FROM portfolios
GROUP BY client_id;