X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 05355D1B53C for ; Mon, 29 Sep 2003 02:08:18 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 68240-08 for ; Sun, 28 Sep 2003 23:07:49 -0300 (ADT) Received: from smtp-1.indo.net.id (smtp-1.indo.net.id [202.159.32.91]) by svr1.postgresql.org (Postfix) with SMTP id D03DFD1B511 for ; Sun, 28 Sep 2003 23:07:40 -0300 (ADT) Received: (qmail 23211 invoked from network); 29 Sep 2003 09:07:42 +0700 Received: from smtp.indo.net.id (202.159.32.81) by smtp-1.indo.net.id with SMTP; 29 Sep 2003 09:07:42 +0700 Received: (qmail 7603 invoked from network); 29 Sep 2003 02:05:28 -0000 Received: from unknown (HELO smtp) (202.159.51.117) by smtp.indo.net.id with SMTP; 29 Sep 2003 02:05:28 -0000 Received: FROM middinkcomp BY smtp ; Mon Sep 29 09:17:54 2003 +0700 Message-ID: <006701c38628$17dba3f0$30179fca@middinkcomp> From: "Muhyiddin A.M Hayat" To: Cc: "Oliver Elphick" Subject: Re: SUM() & GROUP BY Date: Mon, 29 Sep 2003 09:22:04 +0800 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0063_01C3866B.2555ABA0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=1.1 tagged_above=0.0 required=5.0 tests=BAYES_30, HTML_20_30, QUOTED_EMAIL_TEXT, RCVD_IN_OSIRUSOFT_COM X-Spam-Level: * X-Archive-Number: 200309/372 X-Sequence-Number: 15350 This is a multi-part message in MIME format. ------=_NextPart_000_0063_01C3866B.2555ABA0 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0064_01C3866B.2555ABA0" ------=_NextPart_001_0064_01C3866B.2555ABA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable SELECT "public".billing.id, "public".billing.guest_id, "public".billing.trx_date, "public".billing.trx_time, "public".department."name" AS depart, "public".payment_method.description AS payment_method, "public".billing.amount_paid, "public".billing.tax, "public".billing.creator FROM "public".payment_method INNER JOIN "public".billing ON ("public".payment_method.id =3D "public".billing.payment_method) INNER JOIN "public".department ON ("public".billing.dep_id =3D "public".department.id) INNER JOIN "public".billing_items ON ("public".billing.id =3D "public".billing_items.billing_id) Result: id | guest_id | trx_date | trx_time | depart | payment_method |amount_paid | tax | creator ----+----------+------------+----------+--------+----------------+---------- ---+-----+--------- 1 | 1 | 2003-09-28 | 16:08:52 | Resto | Cash |20000.00 | 10 | middink 1 | 1 | 2003-09-28 | 16:08:52 | Resto | Cash |20000.00 | 10 | middink 2 | 1 | 2003-09-29 | 07:50:17 | Resto | Visa |10000.00 | 10 | middink 2 | 1 | 2003-09-29 | 07:50:17 | Resto | Visa |10000.00 | 10 | middink So, i would like to view billing amount, value billing amount sum(item.price) from , so my new query : SELECT "public".billing.id, "public".billing.guest_id, "public".billing.trx_date, "public".billing.trx_time, "public".department."name" AS depart, "public".payment_method.description AS payment_method, "public".billing.tax, (SUM(("public".items.price * "public".billing_items.quantity)-("public".billing_items.discount)) * ("public".billing.tax/100)) AS tax_amount, (SUM(("public".items.price * "public".billing_items.quantity))) AS billing_amount, (SUM(("public".items.price * "public".billing_items.quantity)) - (SUM(("public".items.price * "public".billing_items.quantity))*("public".billing.tax/100)) ) AS total, "public".billing.amount_paid, "public".billing.creator FROM "public".payment_method INNER JOIN "public".billing ON ("public".payment_method.id =3D "public".billing.payment_method) INNER JOIN "public".department ON ("public".billing.dep_id =3D "public".department.id) INNER JOIN "public".billing_items ON ("public".billing.id =3D "public".billing_items.billing_id) INNER JOIN "public".items ON ("public".billing_items.billing_id =3D "public".items.id) GROUP BY "public".billing.id, "public".billing.guest_id, "public".billing.trx_date, "public".billing.trx_time, "public".department."name", "public".payment_method.description, "public".billing.amount_paid, "public".billing.tax, "public".billing.creator Result: id | guest_id | trx_date | trx_time | depart | payment_method | tax |tax_amount | billing_amount | total | amount_paid | creator ----+----------+------------+----------+--------+----------------+-----+---- --------+----------------+-------+-------------+--------- 1 | 1 | 2003-09-28 | 16:08:52 | Resto | Cash | 10 |3600 | 36000.0000 | 32400 | 20000.00 | middink 2 | 1 | 2003-09-29 | 07:50:17 | Resto | Visa | 10 |1200 | 12000.0000 | 10800 | 10000.00 | middink but i have another problem : - how to simple below statment : (SUM(("public".items.price * "public".billing_items.quantity)-("public".billing_items.discount)) * ("public".billing.tax/100)) AS tax_amount, (SUM(("public".items.price * "public".billing_items.quantity))) AS billing_amount, (SUM(("public".items.price * "public".billing_items.quantity)) - (SUM(("public".items.price * "public".billing_items.quantity))*("public".billing.tax/100)) ) AS total, - I have discount in public".billing_items.discount, how to including the discount to billing_amount ----- Original Message -----=20 From: "Oliver Elphick" To: "Muhyiddin A.M Hayat" Cc: Sent: Monday, September 29, 2003 3:56 AM Subject: Re: [SQL] SUM() & GROUP BY > 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? > > > --=20 > 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 > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > "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 > ------=_NextPart_001_0064_01C3866B.2555ABA0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

SELECT
  "public".billing.id,
 =20 "public".billing.guest_id,
  "public".billing.trx_date,
 = =20 "public".billing.trx_time,
  "public".department."name" AS=20 depart,
  "public".payment_method.description AS=20 payment_method,
  "public".billing.amount_paid,
 =20 "public".billing.tax,
  "public".billing.creator
FROM
 = =20 "public".payment_method
  INNER JOIN "public".billing ON=20 ("public".payment_method.id =3D
"public".billing.payment_method)
&nbs= p;=20 INNER JOIN "public".department ON ("public".billing.dep_id=20 =3D
"public".department.id)
  INNER JOIN "public".billing_items = ON=20 ("public".billing.id=20 =3D
"public".billing_items.billing_id)

Result:

 id |= =20 guest_id |  trx_date  | trx_time | depart |=20 payment_method
|amount_paid | tax |=20 creator
----+----------+------------+----------+--------+---------------= -+----------
---+-----+---------
 =20 1 |        1 | 2003-09-28 | 16:08:52 |= =20 Resto  | Cash         &nb= sp;=20 |20000.00 |
10 | middink
  1=20 |        1 | 2003-09-28 | 16:08:52 |=20 Resto  | Cash         &nb= sp;=20 |20000.00 |
10 | middink
  2=20 |        1 | 2003-09-29 | 07:50:17 |=20 Resto  | Visa         &nb= sp;=20 |10000.00 |
10 | middink
  2=20 |        1 | 2003-09-29 | 07:50:17 |=20 Resto  | Visa         &nb= sp;=20 |10000.00 |
10 | middink


So, i would like to view billing amo= unt,=20 value billing amount
sum(item.price) from , so  my new query=20 :


SELECT
  "public".billing.id,
 =20 "public".billing.guest_id,
  "public".billing.trx_date,
 = =20 "public".billing.trx_time,
  "public".department."name" AS=20 depart,
  "public".payment_method.description AS=20 payment_method,
  "public".billing.tax,
 =20 (SUM(("public".items.price=20 *
"public".billing_items.quantity)-("public".billing_items.discount)) =20 *
  ("public".billing.tax/100)) AS tax_amount,
 =20 (SUM(("public".items.price * "public".billing_items.quantity)))
  A= S=20 billing_amount,
  (SUM(("public".items.price *=20 "public".billing_items.quantity))
  -
 =20 (SUM(("public".items.price=20 *
"public".billing_items.quantity))*("public".billing.tax/100))
 = ; =20 )
  AS total,

  "public".billing.amount_paid,
 = =20 "public".billing.creator
FROM
  "public".payment_method
 = ;=20 INNER JOIN "public".billing ON ("public".payment_method.id=20 =3D
"public".billing.payment_method)
  INNER JOIN "public".depar= tment=20 ON ("public".billing.dep_id =3D
"public".department.id)
  INNER = JOIN=20 "public".billing_items ON ("public".billing.id=20 =3D
"public".billing_items.billing_id)
  INNER JOIN "public".ite= ms ON=20 ("public".billing_items.billing_id =3D
"public".items.id)

GROUP= =20 BY
  "public".billing.id,
  "public".billing.guest_id,
&= nbsp;=20 "public".billing.trx_date,
  "public".billing.trx_time,
 = =20 "public".department."name",
 =20 "public".payment_method.description,
 =20 "public".billing.amount_paid,
  "public".billing.tax,
 =20 "public".billing.creator

Result:

 id | guest_id | = =20 trx_date  | trx_time | depart | payment_method | tax
|tax_amount |= =20 billing_amount | total | amount_paid |=20 creator
----+----------+------------+----------+--------+---------------= -+-----+----
--------+----------------+-------+-------------+--------- =20 1 |        1 | 2003-09-28 | 16:08:52 |= =20 Resto  | Cash         &nb= sp;=20 |  10 |3600
|     36000.0000 | 32400=20 |    20000.00 | middink
  2=20 |        1 | 2003-09-29 | 07:50:17 |=20 Resto  | Visa         &nb= sp;=20 |  10 |1200
|     12000.0000 | 10800=20 |    10000.00 | middink


but i have another proble= m=20 :
 - how to simple below statment :
    =20 (SUM(("public".items.price=20 *
"public".billing_items.quantity)-("public".billing_items.discount))   =20 *
    ("public".billing.tax/100)) AS=20 tax_amount,
    (SUM(("public".items.price *=20 "public".billing_items.quantity)))
    AS=20 billing_amount,
    (SUM(("public".items.price *=20 "public".billing_items.quantity))
    -
  &n= bsp;=20 (SUM(("public".items.price=20 *
"public".billing_items.quantity))*("public".billing.tax/100))
 = ;   =20 )
    AS total,

- I have discount in=20 public".billing_items.discount, how to including the
discount to=20 billing_amount

----- Original Message -----
From: "Oliver Elphic= k"=20 <= >
To:=20 "Muhyiddin A.M Hayat" <middink@indo.net.id>
Cc: <pgsql-sql@postgresql.org>
Sent: Monday, September 29, 2003 3:56 AM
Subject: Re: [= SQL]=20 SUM() & GROUP BY


> Any items in the select list need to b= e=20 aggregated (e.g.
> SUM("public".items.price)) or mentioned in the GRO= UP BY=20 list.  Suppose
> there are several billing.guest_id values for e= ach=20 billing.id; which
> value should be listed in the=20 output?
>
>
> --
> Oliver=20 Elphick           &n= bsp;            = ;       =20 Oliver.Elphick@lfix.co.uk
> Isle of Wight,=20 UK            &= nbsp;           &nbs= p;   =20 http://www.lfix.co.uk/oliver
> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D= 4C34=20 3E1D=20 0C1C
>          &nb= sp;      =20 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>   &nbs= p; =20 "Blessed is the man that endureth temptation; for=20 when
>       he is tried, he shall rece= ive=20 the crown of life, which
>       the Lo= rd=20 hath promised to them that love=20 him."
>          &n= bsp;            = ;            &n= bsp;      =20 James 1:12
>
------=_NextPart_001_0064_01C3866B.2555ABA0-- ------=_NextPart_000_0063_01C3866B.2555ABA0 Content-Type: application/octet-stream; name="billing.sql" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="billing.sql" -- EMS PostgreSQL Manager 1.8.0.1 -- --------------------------------------- -- Host : localhost -- Database : hotel -- -- Definition for function plpgsql_call_handler (OID =3D 16976) :=20 -- SET search_path =3D public, pg_catalog; CREATE FUNCTION plpgsql_call_handler () RETURNS language_handler AS '$libdir/plpgsql', 'plpgsql_call_handler' LANGUAGE c; -- -- Definition for language plpgsql (OID =3D 16977) :=20 -- CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler; -- -- Structure for table department (OID =3D 17857) :=20 -- CREATE TABLE department ( id serial NOT NULL, name character varying(50) ); -- -- Structure for table payment_method (OID =3D 17902) :=20 -- CREATE TABLE payment_method ( id serial NOT NULL, description character varying(30) NOT NULL ); -- -- Structure for table items (OID =3D 18094) :=20 -- CREATE TABLE items ( id bigserial NOT NULL, description character varying(50), price numeric(10,2) DEFAULT 0, discount real DEFAULT 0, stock smallint DEFAULT 0 ); -- -- Structure for table guest (OID =3D 18285) :=20 -- CREATE TABLE guest ( id serial NOT NULL, ref_num character varying(50), name character varying(50), address text, phone character varying(50) ); -- -- Structure for table billing (OID =3D 18298) :=20 -- CREATE TABLE billing ( id serial NOT NULL, guest_id integer, trx_date date DEFAULT now(), trx_time time(0) without time zone DEFAULT ('now'::text)::time(6) with = time zone, payment_method integer, tax real, amount_paid numeric(10,2), dep_id integer, creator character varying(50) DEFAULT "current_user"() ); -- -- Structure for table billing_items (OID =3D 18325) :=20 -- CREATE TABLE billing_items ( id serial NOT NULL, billing_id integer NOT NULL, items_id bigint, quantity numeric(10,2) DEFAULT 0 NOT NULL, discount real DEFAULT 0 NOT NULL ); -- -- Data for table public.department (OID =3D 17857) (LIMIT 0,1) -- INSERT INTO department (id, name) VALUES (1, 'Resto'); COMMIT; -- -- Data for table public.payment_method (OID =3D 17902) (LIMIT 0,9) -- INSERT INTO payment_method (id, description) VALUES (1, 'Cash'); INSERT INTO payment_method (id, description) VALUES (2, 'Credit'); INSERT INTO payment_method (id, description) VALUES (3, 'Visa '); INSERT INTO payment_method (id, description) VALUES (4, 'Master Card'); INSERT INTO payment_method (id, description) VALUES (5, 'AmEx'); INSERT INTO payment_method (id, description) VALUES (6, 'JBC'); INSERT INTO payment_method (id, description) VALUES (7, 'Discover'); INSERT INTO payment_method (id, description) VALUES (8, 'Transfer to Guest = Ledger'); INSERT INTO payment_method (id, description) VALUES (9, 'Transfer From Adv = Deposit'); COMMIT; -- -- Data for table public.items (OID =3D 18094) (LIMIT 0,4) -- INSERT INTO items (id, description, price, discount, stock) VALUES (4, 'Aqu= a', 2500.00, 0, 200); INSERT INTO items (id, description, price, discount, stock) VALUES (1, 'Coc= a Cola (Kaleng)', 3000.00, 0, 120); INSERT INTO items (id, description, price, discount, stock) VALUES (2, 'Spr= ite (Kaleng)', 3000.00, 0, 120); INSERT INTO items (id, description, price, discount, stock) VALUES (3, 'Fan= ta (Kaleng)', 3000.00, 0, 120); COMMIT; -- -- Data for table public.guest (OID =3D 18285) (LIMIT 0,4) -- INSERT INTO guest (id, ref_num, name, address, phone) VALUES (1, '21.5005.3= 10879.001', 'Muhyiddin1', 'Jl Singa No 65 Makassar', '628152529671'); INSERT INTO guest (id, ref_num, name, address, phone) VALUES (3, '21.5005',= 'Rien', NULL, '08152529651'); INSERT INTO guest (id, ref_num, name, address, phone) VALUES (5, '22', 'Tes= t', NULL, '55'); INSERT INTO guest (id, ref_num, name, address, phone) VALUES (4, '0081111',= 'Doel', 'Jl Singa', '080989999'); COMMIT; -- -- Data for table public.billing (OID =3D 18298) (LIMIT 0,2) -- INSERT INTO billing (id, guest_id, trx_date, trx_time, payment_method, tax,= amount_paid, dep_id, creator) VALUES (1, 1, '2003-09-28', '16:08:52', 1, 1= 0, 20000.00, 1, 'middink'); INSERT INTO billing (id, guest_id, trx_date, trx_time, payment_method, tax,= amount_paid, dep_id, creator) VALUES (2, 1, '2003-09-29', '07:50:17', 3, 1= 0, 10000.00, 1, 'middink'); COMMIT; -- -- Data for table public.billing_items (OID =3D 18325) (LIMIT 0,4) -- INSERT INTO billing_items (id, billing_id, items_id, quantity, discount) VA= LUES (2, 1, 2, 10.00, 0); INSERT INTO billing_items (id, billing_id, items_id, quantity, discount) VA= LUES (1, 1, 1, 2.00, 0); INSERT INTO billing_items (id, billing_id, items_id, quantity, discount) VA= LUES (4, 2, 2, 2.00, 0); INSERT INTO billing_items (id, billing_id, items_id, quantity, discount) VA= LUES (5, 2, 3, 2.00, 0); COMMIT; -- Definition for index dep_id_pkey (OID =3D 17862) :=20 -- ALTER TABLE ONLY department ADD CONSTRAINT dep_id_pkey PRIMARY KEY (id); -- -- Definition for index payment_method_pkey (OID =3D 17905) :=20 -- ALTER TABLE ONLY payment_method ADD CONSTRAINT payment_method_pkey PRIMARY KEY (id); -- -- Definition for index payment_method_description_key (OID =3D 17907) :=20 -- ALTER TABLE ONLY payment_method ADD CONSTRAINT payment_method_description_key UNIQUE (description); -- -- Definition for index items_pkey (OID =3D 18100) :=20 -- ALTER TABLE ONLY items ADD CONSTRAINT items_pkey PRIMARY KEY (id); -- -- Definition for index guest_pkey (OID =3D 18291) :=20 -- ALTER TABLE ONLY guest ADD CONSTRAINT guest_pkey PRIMARY KEY (id); -- -- Definition for index billing_pkey (OID =3D 18304) :=20 -- ALTER TABLE ONLY billing ADD CONSTRAINT billing_pkey PRIMARY KEY (id); -- -- Definition for index billing_items_pkey (OID =3D 18330) :=20 -- ALTER TABLE ONLY billing_items ADD CONSTRAINT billing_items_pkey PRIMARY KEY (id); ------=_NextPart_000_0063_01C3866B.2555ABA0--