How to use count and group by at the same select statement

Getting a bit fancy: Using variables and distinct counts

For a summary report, that includes a total count along with the grouped data, we'll take advantage of a CROSS JOIN:

/* Save the princess... err, total count in the tower */
SELECT t.columnName, t.count, tt.total
FROM (
    SELECT columnName, COUNT(*) AS count
    FROM tableName
    GROUP BY columnName
) t
/* Let the princes CROSS JOIN the tower */
CROSS JOIN (
    SELECT COUNT(*) as total
    FROM tableName
) tt;


While working with duplicate entries, COUNT(DISTINCT will help eliminate them:

/* There can only be one Highlander! */
SELECT columnName, COUNT(DISTINCT userId) AS uniqueUsers
FROM tableName
GROUP BY columnName;


As a performance top tip, minimize checks within COUNT(), use COUNT(*) over COUNT(column) when simply counting rows.

Going deeper: Using subqueries for detailed analysis

For deeper analysis, subqueries can play a big role. They're like mini tasks within our queries, providing more complex counts:

SELECT sub.columnName, sub.userCount, AVG(sub.userCount) OVER () AS averageCount
FROM (
    /* Let's do some heavy lifting inside our subquery gym */
    SELECT columnName, COUNT(*) AS userCount
    FROM tableName
    GROUP BY columnName
) AS sub;


Remember, OVER () is a part of the analytic function, super effective in databases like Oracle, kind of like a superhero operation on sets of rows.

Visualization

Think of SQL SELECT statement as a fruit market inspector (🕵️‍♂️) assessing fruit varieties:

| Fruit Type | Count |
| ---------- | ----- |
| 🍎 Apples  |   3   |
| 🍌 Bananas |   5   |
| 🍊 Oranges |   2   |

The GROUP BY is like separating fruits into baskets:

GROUP BY fruit_type


🍎🍎🍎 | 🍌🍌🍌🍌🍌 | 🍊🍊

And the COUNT represents our inspector tallying each type:

SELECT fruit_type, COUNT(*)


Inspector's Report (🕵️‍♂️):
- Apples (🍎): 3
- Bananas (🍌): 5
- Oranges (🍊): 2

**Each line in the report represents a fruit type and its count - all from one simple `SELECT` statement.**

Painting with a broader brush: Advanced grouping techniques

Need to analyse complex data relationships? Welcome to the world of both aggregate and window functions:

/* It's time to partition like it's 1999! */
SELECT columnName,
       COUNT(*) OVER (PARTITION BY columnName) AS partitionedCount
FROM tableName;


The PARTITION BY clause allows you to get down to business by performing aggregate operations within individual subsets of rows.

Conditional aggregates can give you more granular counts:

/* Conditional love, SQL style */
SELECT columnName,
       SUM(CASE WHEN condition THEN 1 ELSE 0 END) AS conditionalCount
FROM tableName
GROUP BY columnName;


This fun technique lets you count only the rows matching a certain condition. It's like the VIP section of your query!

Optimization and compatibility: Keep these in mind

Always keep track of official database documentation - they are your SQL bible. It's important to note the slight nuances in functions across various systems like MySQL, PostgreSQL, SQL Server and Oracle. Test your queries for compatibility.

Remember, context is the king. Understand your data and choose wisely between COUNT(*) and COUNT(column).

When it comes to performance, resist the temptation of unnecessary operations in COUNT(). For instance:

/* Remember, slow and steady wins the race */
SELECT columnName, COUNT(1)
FROM tableName
GROUP BY columnName;


Although COUNT(1) might feel intuitive, COUNT(*) generally offers better performance. It listens to the hare and tortoise story - doesn't check column values.