COUNT w SQL: wiersze, NULL, DISTINCT i pułapki JOIN

COUNT odpowiada na bardzo proste pytanie: ile? Kłopot zaczyna się wtedy, gdy dwie osoby mają na myśli różne rzeczy. Jedna liczy klientów, druga zamówienia, a trzecia wiersze po połączeniu zamówień z produktami. Wszystkie zapytania mogą wykonać się poprawnie. Tylko liczby jakoś nie chcą się zgadzać.

Ja zaczynam od ustalenia, co reprezentuje jeden wiersz. Potem wybieram wariant funkcji i sprawdzam mały wynik ręcznie. Pokażę Ci ten sposób na danych syntetycznych. Zobaczysz, dlaczego pusty adres może zostać policzony, dlaczego klient bez zakupów dostaje jedynkę oraz jak uniknąć mnożenia zamówień przez ich pozycje.

Wszystkie poniższe bloki SQL i tabele wyników sprawdziłem w SQLite 3.53.1 przez node:sqlite w Node 24.18.0. Uruchamiaj je kolejno w jednym połączeniu. Różnice dotyczące innych silników opisuję na podstawie ich dokumentacji, bez deklarowania wykonania na nich tych przykładów.

Co dokładnie liczą trzy warianty COUNT

COUNT(*) liczy każdy wiersz wejścia. COUNT(email) liczy tylko te, dla których wyrażenie email nie ma wartości NULL. COUNT(DISTINCT email) liczy różne wartości tego wyrażenia z pominięciem NULL. Taką semantykę opisuje dokumentacja agregatów SQLite.

Celowo piszę o wejściu, a nie zawsze o całej tabeli. W dalszych przykładach wejście zmieni się przez filtr, grupowanie albo złączenie. Funkcja policzy to, co otrzyma. Nie sprawdzi za Ciebie, czy po drodze jedno zamówienie zostało powielone trzy razy.

W nawiasie może być wyrażenie, nie tylko kolumna. COUNT(1) liczy jedynkę dla każdego wiersza, więc wynik jest taki jak przy gwiazdce. COUNT(0) też liczy każdy wiersz, bo zero nie jest NULL. Natomiast COUNT(NULL) daje zero. Liczba wpisana do nawiasu nie wybiera kolumny o tym numerze ani nie określa kroku liczenia.

Przy pytaniu o liczbę rekordów wybieram gwiazdkę, bo najłatwiej odczytać intencję. Przy pytaniu o kompletność pola wskazuję pole. Przy pytaniu o unikalne obiekty najpierw ustalam ich identyfikator. Sama różnorodność nazw klientów zwykle nie jest dobrą definicją liczby klientów.

Wspólne dane do wszystkich przykładów

Poniższy blok uruchom raz w pustym połączeniu testowym. Tworzy trzy tabele tymczasowe. customers ma po jednym wierszu na klienta, orders po jednym na zamówienie, a order_items po jednym na pozycję zamówienia. To trzy różne poziomy szczegółowości. Warto je zapisać, zanim zaczniemy liczyć.

Imiona, adresy w domenie example.com i statusy są wymyślone. Dwa konta mają ten sam adres, jedno ma NULL, a jedno pusty tekst. Zamówienie może mieć nieznany status. Klient Dan nie ma żadnego zamówienia. Celowo zostawiam te sytuacje, bo idealnie czysty przykład niewiele mówi o błędach w raporcie.

-- example: fixture
CREATE TEMP TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT
);
INSERT INTO customers VALUES
    (1, 'Ada', 'ada@example.com'),
    (2, 'Ben', NULL),
    (3, 'Celina', ''),
    (4, 'Dan', 'shared@example.com'),
    (5, 'Ewa', 'shared@example.com');

CREATE TEMP TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT
);
INSERT INTO orders VALUES
    (101, 1, 'paid'),
    (102, 1, 'cancelled'),
    (103, 2, 'paid'),
    (104, 3, NULL),
    (105, 5, 'paid'),
    (106, 5, 'paid');

CREATE TEMP TABLE order_items (
    item_id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL,
    product TEXT NOT NULL
);
INSERT INTO order_items VALUES
    (1001, 101, 'Book'),
    (1002, 101, 'Course'),
    (1003, 102, 'Book'),
    (1004, 103, 'Course'),
    (1005, 104, 'Book'),
    (1006, 105, 'Book'),
    (1007, 105, 'Course'),
    (1008, 105, 'Workbook'),
    (1009, 106, 'Course');

Teraz porównuję trzy warianty na tej samej tabeli:

-- example: variants
SELECT COUNT(*) AS customer_rows,
       COUNT(email) AS nonnull_emails,
       COUNT(DISTINCT email) AS distinct_emails
FROM customers;

Pięć oznacza liczbę kont. Cztery oznacza liczbę wierszy z adresem innym niż NULL. Trzy oznacza różne wartości adresu, wliczając pusty tekst. W żadnym miejscu nie zmieniliśmy danych. Zmieniliśmy pytanie. Dlatego w raporcie używam opisowych nazw miar, zamiast nazywać wszystkie trzy po prostu „liczba klientów”.

NULL, pusty tekst i jakość danych

W tym przykładzie pusty tekst jest wartością. Nie oznacza to, że da się wysłać pod niego wiadomość. COUNT(email) nie sprawdza poprawności adresu, spacji ani składni. Tak samo policzyłby tekst brak. Wynik cztery nie dowodzi więc, że czterech klientów ma użyteczny kontakt.

Mogę przygotować węższą miarę: liczbę adresów, które po usunięciu zwykłych spacji z początku i końca nie są puste. Łączę tutaj TRIM i NULLIF: pierwsza funkcja usuwa spacje, a druga zamienia pusty tekst na NULL. Zwróć uwagę na nazwę wyniku. Nadal nie nazywam go liczbą poprawnych adresów.

-- example: blanks
SELECT COUNT(NULLIF(TRIM(email), '')) AS nonblank_emails,
       COUNT(*) - COUNT(email) AS null_emails
FROM customers;

Pierwsza liczba nadal obejmuje dwa konta ze wspólnym adresem. Druga mierzy wyłącznie NULL, bez pustych napisów. Gdybym pokazał je obok siebie bez definicji, odbiorca mógłby próbować odjąć obie od pięciu i uznać brakujący rekord za błąd raportu.

W praktyce zapisuję osobno liczbę wszystkich rekordów, braków i wartości spełniających uzgodnioną kontrolę. Przed czyszczeniem sprawdzam też powód braku. Pole opcjonalne, uszkodzony import i konto techniczne mogą wyglądać tak samo w kolumnie, a wymagają różnych decyzji. Sama funkcja agregująca tego nie rozstrzygnie.

WHERE wybiera wiersze, GROUP BY tworzy grupy

Zamówienia mają cztery wystąpienia statusu paid. Jeśli interesują mnie wyłącznie one, od razu filtruję wejście:

-- example: where
SELECT COUNT(*) AS paid_orders
FROM orders
WHERE status = 'paid';

Jeżeli potrzebuję rozkładu wszystkich zamówień według klienta, używam grupowania. Każda grupa dostaje własny licznik. Dodatkowo sprawdzam, ile statusów jest znanych:

-- example: groups
SELECT customer_id,
       COUNT(*) AS order_rows,
       COUNT(status) AS known_statuses
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

Klient trzeci ma zamówienie, chociaż jego status jest nieznany. Klient czwarty w ogóle nie pojawia się w wyniku, ponieważ tabela zamówień nie zawiera jego identyfikatora. Grupowanie nie zna pełnej listy klientów i nie dopisze brakujących kategorii.

To ważne przy wykresach. Brak słupka może oznaczać brak wiersza w wyniku, a zero może być rzeczywistą wartością miary. Jeśli chcę pokazać wszystkie osoby, regiony lub dni kalendarza, potrzebuję pełnej listy tych obiektów i odpowiedniego złączenia. Do tego za chwilę wrócę.

HAVING filtruje policzone grupy

Chcę teraz znaleźć klientów z co najmniej dwoma zamówieniami. Najpierw tworzę grupy, potem sprawdzam ich liczebność:

-- example: having
SELECT customer_id, COUNT(*) AS order_rows
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY customer_id;

Jeśli dołożę wcześniej WHERE status = 'paid', pytanie będzie dotyczyć co najmniej dwóch opłaconych zamówień. Ada przestanie spełniać warunek, ponieważ jedno z jej zamówień jest anulowane. Umieszczenie filtra ma więc znaczenie biznesowe, nawet gdy oba zapytania wyglądają bardzo podobnie.

Lubię sprawdzać granicę warunku na konkretnym rekordzie. „Co najmniej dwa” oznacza >= 2, a „więcej niż dwa” oznacza > 2. W naszych danych ten drugi zapis zwróci pusty wynik. Taka różnica potrafi zniknąć w rozmowie, ale w kodzie musi być jednoznaczna. Więcej przykładów znajdziesz w haśle o HAVING.

Pusty zbiór: zero i brak wierszy

Sprawdzam filtr, którego żadne zamówienie nie spełnia. Zwykła agregacja bez grupowania nadal zwraca jeden wiersz:

-- example: empty
SELECT COUNT(*) AS order_rows,
       COUNT(status) AS known_statuses,
       COUNT(DISTINCT status) AS distinct_statuses
FROM orders
WHERE order_id < 0;

Po dodaniu grupowania zachowanie wyniku jest inne:

-- example: empty-groups
SELECT customer_id, COUNT(*) AS order_rows
FROM orders
WHERE order_id < 0
GROUP BY customer_id
ORDER BY customer_id;

Tabela powyżej ma nagłówki i zero wierszy danych. To nie jest jeden rekord z brakującym identyfikatorem. Żadna grupa nie powstała. Rozróżnienie jest istotne dla aplikacji, która próbuje odczytać pierwszy wynik, oraz dla dashboardu, który ma prezentować zero. Opis przetwarzania SELECT rozdziela te przypadki.

Nie dodaję odruchowo COALESCE(COUNT(*), 0), bo samo zliczanie pustego wejścia już daje zero. Gdy nie ma wiersza grupy, podmiana wartości wewnątrz tego nieistniejącego wiersza niczego nie naprawi. Również HAVING może odrzucić wiersz agregacji; obecność COUNT nie gwarantuje wtedy jednego rekordu na wyjściu.

Liczenie warunkowe i pułapka ELSE 0

Często potrzebuję obok siebie liczby opłaconych zamówień, anulowanych oraz tych bez statusu. Mogę przygotować kilka liczników w jednym zapytaniu:

-- example: conditional
SELECT COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_orders,
       COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_orders,
       COUNT(CASE WHEN status IS NULL THEN 1 END) AS unknown_orders,
       COUNT(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS wrong_paid_count
FROM orders;

Ostatnia kolumna jest celowo błędnym sposobem liczenia opłaconych zamówień. CASE zwraca tam jeden albo zero. Obie wartości są różne od NULL, więc COUNT liczy sześć wierszy. Pominięte ELSE w pierwszej kolumnie daje NULL dla pozostałych przypadków i właśnie dlatego licznik działa.

Przy zapisie SUM(CASE ... THEN 1 ELSE 0 END) dodawanie zer ma sens, ale nie należy bezmyślnie przenosić tej konstrukcji do COUNT. Warto też pamiętać o innym zachowaniu SUM dla pustego wejścia. Opisuję je osobno w haśle o SUM.

W SQLite można zapisać taki licznik krócej przez FILTER:

-- example: filter
SELECT COUNT(*) FILTER (WHERE status = 'paid') AS paid_orders
FROM orders;

Wybieram wariant pasujący do silnika i stylu projektu. Nie zakładam, że dostępność FILTER jest identyczna we wszystkich bazach. Niezależnie od zapisu sprawdzam, co stanie się ze statusem nieznanym i nową wartością, której jeszcze nie ma w przykładzie.

LEFT JOIN: policz dopasowania, zachowaj zera

Teraz chcę dostać wszystkich pięciu klientów, również Dana bez zamówień. Punktem wyjścia jest więc tabela klientów:

-- example: left-join
SELECT c.customer_id,
       COUNT(*) AS joined_rows,
       COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY c.customer_id;

Wiersz Dana musi przetrwać złączenie. Kolumny zamówienia dostają w nim NULL. Gwiazdka liczy zachowany wiersz, a identyfikator zamówienia go pomija. Dlatego do liczenia dopasowań wskazuję klucz, który dla prawdziwego zamówienia jest zawsze wypełniony. COUNT(o.status) byłby złym zamiennikiem, bo zgubiłby istniejące zamówienie Celiny.

Przy liczbie opłaconych zamówień filtr statusu umieszczam w warunku dopasowania:

-- example: paid-left-join
SELECT c.customer_id, COUNT(o.order_id) AS paid_orders
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id AND o.status = 'paid'
GROUP BY c.customer_id
ORDER BY c.customer_id;

Przeniesienie warunku do WHERE o.status = 'paid' usunęłoby klientów bez pasującego zamówienia. Czasem właśnie taki wynik jest potrzebny, ale wtedy raport nie pokazuje pełnej listy z zerami. Osobny tekst o LEFT JOIN rozwija tę różnicę na kolejnych przykładach.

DISTINCT po JOIN: kiedy pomaga, a kiedy ukrywa problem

Łączę teraz zamówienia z pozycjami. Zamówienie 101 ma dwie pozycje, a 105 trzy. Po złączeniu każde z nich pojawi się więcej niż raz:

-- example: multiplied
SELECT COUNT(*) AS item_rows,
       COUNT(o.order_id) AS repeated_order_ids,
       COUNT(DISTINCT o.order_id) AS distinct_orders
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id;

Dziewięć jest poprawną liczbą pozycji. Sześć jest poprawną liczbą zamówień obecnych w tym złączeniu. DISTINCT działa tutaj, ponieważ identyfikator reprezentuje obiekt, który naprawdę chcę policzyć. Gdybym zamiast niego policzył różne statusy, odpowiedziałbym na zupełnie inne pytanie.

Nie traktuję jednak DISTINCT jako uniwersalnego plastra. Dodatkowe złączenie mogło usunąć część zamówień, a deduplikacja ich nie przywróci. W naszych danych każde zamówienie ma pozycję, ale prawdziwy system może mieć szkice bez pozycji. Najpierw sprawdzam kardynalność i rodzaj złączenia, potem dobieram licznik.

Jeśli interesują mnie zamówienia zawierające przynajmniej jedną pozycję, mogę uniknąć mnożenia wierszy:

-- example: exists
SELECT COUNT(*) AS orders_with_items
FROM orders AS o
WHERE EXISTS (
    SELECT 1 FROM order_items AS i WHERE i.order_id = o.order_id
);

EXISTS wyraża tu dokładnie potrzebę istnienia dopasowania. Nie obiecuję, że zawsze będzie szybsze. Wybieram je ze względu na czytelność znaczenia, a wydajność sprawdzam na rzeczywistym planie i danych. To odrębne pytania, które warto rozwiązywać w tej kolejności.

Unikalne pary i licznik obok szczegółów

Co jeśli chcę policzyć różne pary klient i status? Zamiast sklejać wartości w jeden napis, najpierw wybieram różne pary, a potem liczę ich wiersze:

-- example: distinct-pairs
SELECT COUNT(*) AS customer_status_pairs
FROM (
    SELECT DISTINCT customer_id, status FROM orders
) AS pairs;

Para klienta trzeciego z NULL zostaje uwzględniona. Dwa opłacone zamówienia Ewy tworzą jedną parę. Taką definicję trzeba ustalić świadomie. Jeśli nieznane statusy mają być pomijane, filtr powinien znaleźć się wewnątrz podzapytania. Sklejanie tekstów może natomiast tworzyć kolizje kluczy i mieszać zachowanie braków z formatowaniem.

Gdy chcę zostawić osobny wiersz każdego zamówienia i dopisać liczbę zamówień klienta, używam funkcji okna:

-- example: window
SELECT order_id, customer_id,
       COUNT(*) OVER (PARTITION BY customer_id) AS customer_orders
FROM orders
ORDER BY order_id;

Wynik nadal ma sześć wierszy. Nie sumuj później kolumny customer_orders jako liczby zamówień, bo licznik powtarza się przy szczegółach. ORDER BY na końcu ustala kolejność prezentacji. Celowo nie dodaję go do OVER, gdzie mógłby zmienić ramę liczenia. Składnię i ograniczenia opisuje dokumentacja okien SQLite; między innymi nie obsługuje ona DISTINCT wewnątrz funkcji okna.

Dialekt i wydajność bez magicznych obietnic

Podstawowe warianty są podobne w wielu bazach, ale typ wyniku i rozszerzenia wymagają sprawdzenia. PostgreSQL 18 zwraca bigint. W SQL Server COUNT zwraca int, a COUNT_BIG zwraca bigint. Dla wielkich zbiorów ma to znaczenie; zwykły licznik SQL Server może przekroczyć zakres typu.

MySQL 8.4 dokumentuje wieloargumentowe COUNT(DISTINCT ...), które pomija kombinacje z NULL. Nie przenoszę tego zapisu do SQLite. Pokazane wyżej podzapytanie z różnymi parami ma jawną semantykę, łącznie z zachowaniem nieznanego statusu.

Nie zakładam automatycznego przyspieszenia po zamianie gwiazdki na jedynkę lub dodaniu indeksu. Pytam, jaki filtr stosuję, ile wierszy muszę przeczytać i czy złączenie produkuje zbędne powtórzenia. W SQLite zaczynam od EXPLAIN QUERY PLAN, które pokazuje między innymi użycie indeksów. Następnie mierzę reprezentatywne zapytanie. Wynik na pięciu klientach służy do sprawdzenia poprawności, a nie do przewidywania czasu na milionach.

Przy porównywaniu dwóch raportów sprawdzam także moment odczytu. Jeżeli między zapytaniami dopisano zamówienia, różne liczniki mogą być poprawne dla różnych stanów danych. Zapisuję filtr, definicję obiektu i zakres czasu razem z wynikiem. Sama etykieta „COUNT” nie wystarcza do odtworzenia takiej analizy.

Ćwiczenie: ilu klientów naprawdę kupiło

Na tych samych danych policz: wszystkich klientów, klientów z przynajmniej jednym opłaconym zamówieniem oraz klientów bez takiego zamówienia. Ewa ma zostać policzona raz, chociaż kupiła dwa razy. Celina i Dan mają trafić do ostatniej grupy. Nie usuwaj zamówień ani nie zmieniaj danych, żeby ułatwić sobie zadanie.

Najpierw zapisz oczekiwany wynik ręcznie. Potem spróbuj zbudować listę identyfikatorów kupujących. Dopiero na końcu policz listę. Jeśli dostaniesz czterech kupujących, najpewniej policzyłeś zamówienia zamiast klientów. To bardzo dobry błąd do popełnienia na sześciu rekordach, zanim powędruje do prezentacji.

Jedno z rozwiązań najpierw sprowadza opłacone zamówienia do jednego wiersza na klienta:

-- example: exercise
WITH buyers AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE status = 'paid'
)
SELECT COUNT(*) AS customers,
       COUNT(b.customer_id) AS buyers,
       COUNT(*) - COUNT(b.customer_id) AS nonbuyers
FROM customers AS c
LEFT JOIN buyers AS b ON b.customer_id = c.customer_id;

Sprawdź jeszcze dwa warianty myślowe: kolejne opłacone zamówienie Ewy oraz pierwsze opłacone zamówienie Dana. W pierwszym liczba kupujących powinna zostać bez zmian. W drugim powinna wzrosnąć o jeden, a liczba niekupujących spaść o jeden. Takie kontrole granic dają znacznie więcej niż zadowolenie, że zapytanie nie wyświetliło błędu.

Kiedy będziesz używać COUNT we własnym raporcie, nazwij obiekt, sprawdź NULL, obejrzyj kilka wierszy po złączeniach i porównaj wynik z ręcznym oczekiwaniem. Właśnie ten krótki proces sprawia, że liczba w tabeli zaczyna znaczyć to, co obiecuje jej nagłówek.