What is the difference between COUNT(*) and COUNT(columnname) after joining tables?

user_18450581 0
Viewed 40

The first code like this

SELECT s.name, w.channel, COUNT(*) num_events
FROM accounts a
JOIN web_events w
ON a.id = w.account_id
JOIN sales_reps s
ON s.id = a.sales_rep_id
GROUP BY s.name, w.channel
ORDER BY num_events DESC;

The second code like this

SELECT s.name, w.channel, COUNT(w.channel) 
FROM web_events w
JOIN accounts a
ON a.id = w.account_id
JOIN sales_reps s
ON s.id = a.sales_rep_id
GROUP BY s.name, w.channel
ORDER BY COUNT(w.channel) DESC

Both codes have the same results. But why? What is different from COUNT(*) num_events to COUNT(w.channel)?

sql
2 Answers

If you are using MySQL,then we can check MySQL official document for function_count,and will found below

COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values.

COUNT(DISTINCT expr,[expr...]) Returns a count of the number of rows with different non-NULL expr values.

So COUNT(w.channel) will get total records the query fetch and w.channel is not null

user_31764190

  • COUNT (*) counts all the row

  • COUNT (field) counts rows that field value is not NULL

When i say NULL, i strictly mean NULL. Check below sql

You can see COUNT function counts empty string on postgrsql (empty string is not same as NULL). But in Oracle database empty strings is same as NULL

WITH temp AS (SELECT 1 AS a, 'x' AS b, 1 AS c
              UNION ALL
              SELECT 2 AS a, 'x' AS b, 1 AS c
              UNION ALL
              SELECT 3 AS a, 'y' AS b, 1 AS c
              UNION ALL
              SELECT 4 AS a, 'y' AS b, 1 AS c
              UNION ALL
              SELECT 5 AS a, '' AS b, NULL AS c)
SELECT count( * ) AS "count(*)"
     , count( DISTINCT b ) AS "count( distinct something )"
     , count( c ) AS "count (something)"
FROM temp

Result on postgresql

count(*)       count( distinct something )           count (something)
--------       --------------------------            -----------------
 5                      3                                4

Result on oracle

count(*)       count( distinct something )           count (something)
--------       --------------------------            -----------------
 5                      2                                4
user_123055530
Related