Answer to question
SELECT DISTINCT ON (name, zonedistrict_id)
ST_Union(geom) as geom, gid, name, zonedistrict_id, zonestyle_id, longname
FROM zones
GROUP BY gid, name, zonedistrict_id, zonestyle_id, longname
ORDER BY name, zonedistrict_id, zonestyle_id;
It depends on what you are actually trying to achieve and what version of Postgres you are using and the table definition of the underlying table. You chose not to disclose any of that.
This gives you one distinct row per (name, zonedistrict_id)
, but geom
is only the aggregate over (gid, name, zonedistrict_id, zonestyle_id, longname)
In older versions you had to list every non-aggregated column of the SELECT
list in the GROUP BY
list. This changed somewhat with PostgreSQL 9.1. I quote the release notes:
Allow non-GROUP BY
columns in the query target list when the primary
key is specified in the GROUP BY
clause (Peter Eisentraut)
My guess
However, I suspect you secretly want this:
SELECT DISTINCT ON (name, zonedistrict_id)
ST_Union(geom) OVER (PARTITION BY name, zonedistrict_id) AS geom
, gid, name, zonedistrict_id, zonestyle_id, longname
FROM zones
ORDER BY name, zonedistrict_id, zonestyle_id;
Using a ST_Union(geom)
as aggregate window function. So geom
is actually the aggregate over (name, zonedistrict_id)
, which seems more likely.
This should be possible since the PostGis manual tells us about its aggregate functions:
... can be used just like any other sql aggregate function such as sum, average.
And SQL aggregate functions can be used as window functions.
The problem is that you are not joining your two tables based on band_id
, as a result you are creating a Cartesian product. When you create this cartesian product, you are combining each row from your placing
table to each row in the band
table.
For example, if you have data similar to the following:
create table band
(
band_id number
);
insert into band values (1);
insert into band values (2);
insert into band values (3);
insert into band values (4);
insert into band values (5);
create table placing
(
band_id number,
points number
);
insert into placing values (1, 10);
insert into placing values (1, 2);
insert into placing values (2, 1);
insert into placing values (3, 3);
insert into placing values (3, 89);
insert into placing values (4, 45);
insert into placing values (5, 63);
And you query the data without a join on the band_id
column:
SELECT
placing.points,
band.band_id
FROM placing, band;
You are generating data similar to:
| POINTS | BAND_ID |
|--------|---------|
| 10 | 1 |
| 2 | 1 |
| 1 | 1 |
| 3 | 1 |
| 89 | 1 |
| 45 | 1 |
| 63 | 1 |
See Demo. As you can see band_id = 1
now has every single point
value from the placing
table even though your really only have points = 10
and points=2
.
In order to get the correct result, you need to JOIN
the two tables on the band_id
column. Your query will be similar to the following:
SELECT
SUM(p.points) AS sumpoints,
b.band_id
FROM placing p
INNER JOIN band b
on p.band_id = b.band_id
GROUP BY b.band_id
ORDER BY SUM(p.points) DESC;
See SQL Fiddle with Demo. This will give a result of:
| SUMPOINTS | BAND_ID |
|-----------|---------|
| 92 | 3 |
| 63 | 5 |
| 45 | 4 |
| 12 | 1 |
| 1 | 2 |
Best Answer
You can add the number of gifts with either:
or the similar (works in MySQL only):
If you want a simple (yes/no) as in your question, if the customer has ever done one or more gifts vs. none at all, then: