From: Oliver Elphick <olly@lfix.co.uk>
To: Muhyiddin A.M Hayat <middink@indo.net.id>
Cc: pgsql-sql@postgresql.org
Subject: Re: SUM() & GROUP BY
Date: Sun, 28 Sep 2003 20:56:56 +0100
Message-ID: <1064779015.1458.4.camel@linda.lfix.co.uk> (raw)
In-Reply-To: <001401c385ea$836f5960$29179fca@middinkcomp>
References: <001401c385ea$836f5960$29179fca@middinkcomp>
On Sun, 2003-09-28 at 19:01, Muhyiddin A.M Hayat wrote:
>
> hotel=# SELECT
> hotel-# "public".billing.id,
> hotel-# "public".billing.guest_id,
> hotel-# "public".billing.trx_date,
> hotel-# "public".billing.trx_time,
> hotel-# "public".billing.payment_method,
> hotel-# "public".billing.tax,
> hotel-# "public".billing.dep_id,
> hotel-# "public".department."name",
> hotel-# SUM("public".items.price) AS total,
> hotel-# "public".billing.amount_paid
> hotel-# FROM
> hotel-# "public".billing_items
> hotel-# INNER JOIN "public".billing ON
> ("public".billing_items.billing_id = "public".billing.id)
> hotel-# INNER JOIN "public".department ON ("public".billing.dep_id =
> "public".department.id)
> hotel-# INNER JOIN "public".items ON
> ("public".billing_items.items_id = "public".items.id)
> hotel-# GROUP BY "public".billing.id;
> ERROR: Attribute billing.guest_id must be GROUPed or used in an
> aggregate function
> hotel=#
>
> What Worng ??
Any items in the select list need to be aggregated (e.g.
SUM("public".items.price)) or mentioned in the GROUP BY list. Suppose
there are several billing.guest_id values for each billing.id; which
value should be listed in the output?
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Blessed is the man that endureth temptation; for when
he is tried, he shall receive the crown of life, which
the Lord hath promised to them that love him."
James 1:12
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: olly@lfix.co.uk, middink@indo.net.id
Subject: Re: SUM() & GROUP BY
In-Reply-To: <1064779015.1458.4.camel@linda.lfix.co.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox