CODEWITHSUNDEEP
postgresqlrelational-database
Matthew Kaye
Matthew Kaye

Reputation: 465

SQL count one row if many exist

I have what I think is a pretty standard Postgres SQL problem. I can explain it, just can't program it.

I have 4 rows that have unique account IDs. Each of these rows shares the same longitude and latitude values, but their physical addresses differ (apartment 1A, apartment 2B.....). Each account has status of active or inactive. Each account either gets or does not get HBO.

This data is all derived from a single table. My condition is this: Grouping by latitude, longitude and the name of the borough, loop the data and count one row where every account is inactive and at least one of the accounts is still getting HBO.

So if there are 4 accounts at the same lng/lat, 3 are inactive but 1 is still active, the record is skipped.

If there are 4 accounts at the same lng/lat, 4 are inactive, none get HBO, the record is skipped.

If there are 4 accounts at the same lng/lat, 4 are inactive, one still gets HBO, the record is counted.

UPDATE: I need this as a count by town. I just used the lat/lng to confirm that the logic was correct and it is. The final result I'm looking for is this:

Borough | Count
---------------------
Bronx      50
Queens     12

Upvotes: 0

Views: 119

Answers (2)

jimmu
jimmu

Reputation: 1056

From what I understand you would like to filter rows where inactive count = 4 and count of active hbo account > 0. I count the number of inactive and hbo accounts separately and filter rows accordingly. Similar to Gordon's solution.

SELECT lat, lon
FROM test
GROUP BY 1, 2
HAVING COUNT(CASE WHEN IS_active THEN 1 END) = 0 
    AND COUNT(CASE WHEN has_hbo THEN 1 END) > 0;

After this, you can group by borough and take the count. Here's SQL Fiddle.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270081

You can get the lat/long pairs using GROUP BY and HAVING:

SELECT ml."LATITUDE", ml."LONGITUDE"
FROM "MasterListMN" ml
GROUP BY ml."LATITUDE", ml."LONGITUDE"
HAVING SUM( ("account_part_1" = 'INACT' AND "account_part_2" = 'INACT')::int) = COUNT(*) AND
       SUM( (ml."GETS_HBO" = 'TRUE')::int) > 0;

Upvotes: 1

Related Questions