CODEWITHSUNDEEP
mysqlsqlgroup-by
Donald
Donald

Reputation: 145

SQL Count total of addresses

Here is my data

Vehicle    | Street       | Number | Postcode
Car          Fakestreet      3       0010NK
Motorbike    Fakestreet      3       0010NK
Car          Fakestreet      4       0010NK
Car          Fakestreet      1       0010NK
Companycar   Fakestreet      1       0010NK

I want to count unique adresses per postcode so this is want I want to see:

Postcode | Total_adresses
0010NK   | 3

I thought this must be a simple query so I have this:

SELECT Postcode, COUNT(*) AS total_adresses
FROM my_database
WHERE Postcode = '0010NK'
GROUP BY Postcode, Street, Number

But I get this result:

Postcode | total_adresses
0010NK   | 2
0010NK   | 2
0010NK   | 1

I can't get my head around why it doesn't give me the desired result. Any ideas why?

Upvotes: 0

Views: 715

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Just aggregate by the one column:

SELECT Postcode, COUNT(*) AS total_adresses
FROM my_database
WHERE Postcode = '0010NK'
GROUP BY Postcode;

The combination of keys in the GROUP BY defines each row in the aggregated result set. You want only one row per Postcode, so that is the only key you should have in the GROUP BY.

EDIT:

To count the number of distinct addresses, use COUNT(DISTINCT). Here is one way:

SELECT Postcode, COUNT(DISTINCT CONCAT(STREET, ' ', NUMBER, ' ', POSTALCODE) AS total_adresses
FROM my_database
WHERE Postcode = '0010NK'
GROUP BY Postcode;

Some databases support:

SELECT Postcode, COUNT(DISTINCT STREET, NUMBER, POSTALCODE) AS total_adresses

Upvotes: 1

jarlh
jarlh

Reputation: 44796

Have a derived table to return the unique addresses. Then GROUP BY its result:

select Postcode, count(*)
from
(
    SELECT DISTINCT Street, Number, Postcode
    FROM my_database
) dt
group by Postcode

Upvotes: 1

Related Questions