From middink@indo.net.id Sun Sep 28 18:47:29 2003 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 09AC6D1B526 for ; Sun, 28 Sep 2003 18:47:29 +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 99961-06 for ; Sun, 28 Sep 2003 15:46:59 -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 BFED7D1B550 for ; Sun, 28 Sep 2003 15:46:54 -0300 (ADT) Received: (qmail 32045 invoked from network); 29 Sep 2003 01:46:45 +0700 Received: from smtp.indo.net.id (202.159.32.81) by smtp-1.indo.net.id with SMTP; 29 Sep 2003 01:46:45 +0700 Received: (qmail 12131 invoked from network); 28 Sep 2003 18:44:31 -0000 Received: from unknown (HELO smtp) (202.159.51.117) by smtp.indo.net.id with SMTP; 28 Sep 2003 18:44:31 -0000 Received: FROM middinkcomp BY smtp ; Mon Sep 29 01:57:06 2003 +0700 Message-ID: <001401c385ea$836f5960$29179fca@middinkcomp> From: "Muhyiddin A.M Hayat" To: Subject: SUM() & GROUP BY Date: Mon, 29 Sep 2003 02:01:14 +0800 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0010_01C3862D.900E1C40" 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=2.0 tagged_above=0.0 required=5.0 tests=HTML_20_30, SUBJ_ALL_CAPS X-Spam-Level: ** X-Archive-Number: 200309/363 X-Sequence-Number: 15341 This is a multi-part message in MIME format. ------=_NextPart_000_0010_01C3862D.900E1C40 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable hotel=3D# 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= =3D "public".billing.id) hotel-# INNER JOIN "public".department ON ("public".billing.dep_id =3D "p= ublic".department.id) hotel-# INNER JOIN "public".items ON ("public".billing_items.items_id =3D= "public".items.id) hotel-# GROUP BY "public".billing.id; ERROR: Attribute billing.guest_id must be GROUPed or used in an aggregate = function hotel=3D# What Worng ?? How to using SUM() & GROUP BY ------=_NextPart_000_0010_01C3862D.900E1C40 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
 
hotel=3D# SELECT
hotel-#  =20 "public".billing.id,
hotel-#  =20 "public".billing.guest_id,
hotel-#  =20 "public".billing.trx_date,
hotel-#  =20 "public".billing.trx_time,
hotel-#  =20 "public".billing.payment_method,
hotel-#  =20 "public".billing.tax,
hotel-#  =20 "public".billing.dep_id,
hotel-#  =20 "public".department."name",
hotel-#   SUM("public".items.price= ) AS=20 total,
hotel-#   "public".billing.amount_paid
hotel-#=20 FROM
hotel-#   "public".billing_items
hotel-#   I= NNER=20 JOIN "public".billing ON ("public".billing_items.billing_id =3D=20 "public".billing.id)
hotel-#   INNER JOIN "public".department = ON=20 ("public".billing.dep_id =3D "public".department.id)
hotel-#  = INNER=20 JOIN "public".items ON ("public".billing_items.items_id =3D=20 "public".items.id)
hotel-# GROUP BY =20 "public".billing.id;
ERROR:  Attribute billing.guest_id must be GRO= UPed=20 or used in an aggregate function
hotel=3D#
 
What Worng ??
 
How to using SUM() & GROUP=20 BY
------=_NextPart_000_0010_01C3862D.900E1C40-- From olly@lfix.co.uk Sun Sep 28 19:57:12 2003 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 D39E1D1B4E1 for ; Sun, 28 Sep 2003 19:57:12 +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 12087-02 for ; Sun, 28 Sep 2003 16:57:04 -0300 (ADT) Received: from anchor-post-37.mail.demon.net (anchor-post-37.mail.demon.net [194.217.242.87]) by svr1.postgresql.org (Postfix) with ESMTP id 963C1D1B52D for ; Sun, 28 Sep 2003 16:57:01 -0300 (ADT) Received: from lfix.demon.co.uk ([80.177.205.209] helo=cerberus.lfix.co.uk) by anchor-post-37.mail.demon.net with esmtp (Exim 3.35 #1) id 1A3hfC-0006uC-0b; Sun, 28 Sep 2003 20:57:02 +0100 Received: from linda.lfix.co.uk ([192.168.1.1]) by cerberus.lfix.co.uk with esmtp (Exim 4.22) id 1A3hew-0004qe-8K; Sun, 28 Sep 2003 20:56:46 +0100 Subject: Re: SUM() & GROUP BY From: Oliver Elphick To: "Muhyiddin A.M Hayat" Cc: pgsql-sql@postgresql.org In-Reply-To: <001401c385ea$836f5960$29179fca@middinkcomp> References: <001401c385ea$836f5960$29179fca@middinkcomp> Content-Type: text/plain Organization: LFIX Limited Message-Id: <1064779015.1458.4.camel@linda.lfix.co.uk> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 Date: Sun, 28 Sep 2003 20:56:56 +0100 Content-Transfer-Encoding: 7bit X-LFIX-MailScanner: Found to be clean X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/368 X-Sequence-Number: 15346 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 From middink@indo.net.id Mon Sep 29 02:06:21 2003 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 9CA08D1B519 for ; Mon, 29 Sep 2003 02:06:21 +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 68532-05 for ; Sun, 28 Sep 2003 23:05:54 -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 06CB6D1B52E for ; Sun, 28 Sep 2003 23:05:46 -0300 (ADT) Received: (qmail 22520 invoked from network); 29 Sep 2003 09:05:46 +0700 Received: from smtp.indo.net.id (202.159.32.81) by smtp-1.indo.net.id with SMTP; 29 Sep 2003 09:05:46 +0700 Received: (qmail 6447 invoked from network); 29 Sep 2003 02:03:32 -0000 Received: from unknown (HELO smtp) (202.159.51.117) by smtp.indo.net.id with SMTP; 29 Sep 2003 02:03:32 -0000 Received: FROM middinkcomp BY smtp ; Mon Sep 29 09:15:56 2003 +0700 Message-ID: <004d01c38627$d14d0c80$30179fca@middinkcomp> From: "Muhyiddin A.M Hayat" To: Cc: "Oliver Elphick" References: <001401c385ea$836f5960$29179fca@middinkcomp> <1064779015.1458.4.camel@linda.lfix.co.uk> Subject: Re: SUM() & GROUP BY Date: Mon, 29 Sep 2003 09:20:04 +0800 MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPart_000_0046_01C3866A.DE1688E0" 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-Archive-Number: 200309/371 X-Sequence-Number: 15349 This is a multi-part message in MIME format. ------=_NextPart_000_0046_01C3866A.DE1688E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit so 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 = "public".billing.payment_method) INNER JOIN "public".department ON ("public".billing.dep_id = "public".department.id) INNER JOIN "public".billing_items ON ("public".billing.id = "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 = "public".billing.payment_method) INNER JOIN "public".department ON ("public".billing.dep_id = "public".department.id) INNER JOIN "public".billing_items ON ("public".billing.id = "public".billing_items.billing_id) INNER JOIN "public".items ON ("public".billing_items.billing_id = "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 ----- 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? > > > -- > 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 > ------=_NextPart_000_0046_01C3866A.DE1688E0 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_0046_01C3866A.DE1688E0-- From middink@indo.net.id Mon Sep 29 02:08:18 2003 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-- From martinkuria@hotmail.com Fri May 7 06:47:57 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 80DA0D1BAF5 for ; Fri, 7 May 2004 03:47:57 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 21104-09 for ; Fri, 7 May 2004 03:47:37 -0300 (ADT) Received: from hotmail.com (sea2-f26.sea2.hotmail.com [207.68.165.26]) by svr1.postgresql.org (Postfix) with ESMTP id 544B6D1ECC8 for ; Fri, 7 May 2004 03:47:35 -0300 (ADT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Thu, 6 May 2004 23:47:36 -0700 Received: from 80.205.253.69 by sea2fd.sea2.hotmail.msn.com with HTTP; Fri, 07 May 2004 06:47:35 GMT X-Originating-IP: [80.205.253.69] X-Originating-Email: [martinkuria@hotmail.com] X-Sender: martinkuria@hotmail.com From: "Martin Kuria" To: olly@lfix.co.uk, middink@indo.net.id Cc: pgsql-sql@postgresql.org Subject: Re: SUM() & GROUP BY Date: Fri, 07 May 2004 09:47:35 +0300 Mime-Version: 1.0 Content-Type: text/plain; format=flowed Message-ID: X-OriginalArrivalTime: 07 May 2004 06:47:36.0085 (UTC) FILETIME=[2E5EAC50:01C433FF] X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/62 X-Sequence-Number: 17776 Hi again I have two tables I would like to query i.e. service table and division table SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s GROUP BY s.pd_geo; OUTPUT: pd_geo | count ---------------------- 1 | 49 2 | 39 3 | 6 4 | 54 SELECT d.divisions_name, d.divisions_id) FROM ser s, ser_divisions d WHERE d.divisions_id = s.ser_divisions; division_name | divisions_id -------------------------------------- DEC | 6 DEPI | 7 DRC | 8 How can I create a query that displays How the divisions answered the question please do assist. Regrards Martin W. Kuria >From: Oliver Elphick <olly@lfix.co.uk> >To: "Muhyiddin A.M Hayat" <middink@indo.net.id> >CC: pgsql-sql@postgresql.org >Subject: Re: [SQL] SUM() & GROUP BY >Date: Sun, 28 Sep 2003 20:56:56 +0100 > >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 > > >---------------------------(end of broadcast)--------------------------- >TIP 8: explain analyze is your friend _________________________________________________________________ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail From dev@archonet.com Fri May 7 08:01:08 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 62D00D1ED0D for ; Fri, 7 May 2004 05:01:08 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 49664-08 for ; Fri, 7 May 2004 05:00:49 -0300 (ADT) Received: from anchor-post-30.mail.demon.net (anchor-post-30.mail.demon.net [194.217.242.88]) by svr1.postgresql.org (Postfix) with ESMTP id B15A1D1F33C for ; Fri, 7 May 2004 05:00:47 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-30.mail.demon.net with esmtp (Exim 3.35 #1) id 1BM0Hn-0005sV-0U; Fri, 07 May 2004 09:00:48 +0100 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 471B5167FE; Fri, 7 May 2004 09:00:44 +0100 (BST) Message-ID: <409B422B.6080502@archonet.com> Date: Fri, 07 May 2004 09:00:43 +0100 From: Richard Huxton User-Agent: Mozilla Thunderbird 0.6 (X11/20040502) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Martin Kuria Cc: olly@lfix.co.uk, middink@indo.net.id, pgsql-sql@postgresql.org Subject: Re: SUM() & GROUP BY References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/63 X-Sequence-Number: 17777 Martin Kuria wrote: > Hi again I have two tables I would like to query i.e. service table and > division table > > SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s GROUP BY s.pd_geo; > OUTPUT: > pd_geo | count > ---------------------- > 1 | 49 > 2 | 39 > 3 | 6 > 4 | 54 > > SELECT d.divisions_name, d.divisions_id) > FROM ser s, ser_divisions d > WHERE d.divisions_id = s.ser_divisions; > > division_name | divisions_id > -------------------------------------- > DEC | 6 > DEPI | 7 > DRC | 8 > > How can I create a query that displays How the divisions answered the > question please do assist. Martin - you'll need to explain exactly what you want. Can you show what outputs you would like given the above data? -- Richard Huxton Archonet Ltd From martinkuria@hotmail.com Fri May 7 11:32:50 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 19463D1DB81 for ; Fri, 7 May 2004 08:32:50 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 29576-04 for ; Fri, 7 May 2004 08:32:31 -0300 (ADT) Received: from hotmail.com (sea2-f17.sea2.hotmail.com [207.68.165.17]) by svr1.postgresql.org (Postfix) with ESMTP id 51A67D1E2CA for ; Fri, 7 May 2004 08:32:28 -0300 (ADT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Fri, 7 May 2004 04:32:31 -0700 Received: from 80.205.253.69 by sea2fd.sea2.hotmail.msn.com with HTTP; Fri, 07 May 2004 11:32:31 GMT X-Originating-IP: [80.205.253.69] X-Originating-Email: [martinkuria@hotmail.com] X-Sender: martinkuria@hotmail.com From: "Martin Kuria" To: dev@archonet.com Cc: olly@lfix.co.uk, middink@indo.net.id, pgsql-sql@postgresql.org Subject: Re: SUM() & GROUP BY Date: Fri, 07 May 2004 14:32:31 +0300 Mime-Version: 1.0 Content-Type: text/plain; format=flowed Message-ID: X-OriginalArrivalTime: 07 May 2004 11:32:31.0436 (UTC) FILETIME=[FBFE38C0:01C43426] X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/69 X-Sequence-Number: 17783 Thanks Huxton, Sorry for not explaining fully here is what I would like to achieve: When I do: SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s GROUP BY s.pd_geo; OUTPUT: pd_geo | count ---------------------- 1 | 49 2 | 39 3 | 6 4 | 54 It outputs the number of entries the Divisions have made Here is what Division table contains: SELECT * FROM ser_divisions; divisions_name | divisions_id --------------------------------------- DEE | 3131 DEPI | 3133 DED | 3134 GBH | 3136 Now I would like to get to know how each Division answered i.e. SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s WHERE s.ser_divisions = '3131' GROUP BY s.pd_geo; output: pd_geo | count ---------------------- 1 | 9 2 | 2 3 | 6 4 | 5 But this is the output I intend to get: divisions_name | pd_geo | count ----------------------------------------------- DEE | 1 | 9 DEE | 2 | 2 DEE | 3 | 6 DEE | 4 | 5 How do I achieve the above results please do advice thanks again. Kind Regards +-----------------------------------------------------+ | Martin W. Kuria (Mr.) martin.kuria@unon.org +----------------------------------------------------+ >From: Richard Huxton <dev@archonet.com> >To: Martin Kuria <martinkuria@hotmail.com> >CC: olly@lfix.co.uk, middink@indo.net.id, pgsql-sql@postgresql.org >Subject: Re: [SQL] SUM() & GROUP BY >Date: Fri, 07 May 2004 09:00:43 +0100 > >Martin Kuria wrote: >>Hi again I have two tables I would like to query i.e. service table >>and division table >> >>SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s GROUP BY s.pd_geo; >>OUTPUT: >>pd_geo | count >>---------------------- >> 1 | 49 >> 2 | 39 >> 3 | 6 >> 4 | 54 >> >>SELECT d.divisions_name, d.divisions_id) >>FROM ser s, ser_divisions d >>WHERE d.divisions_id = s.ser_divisions; >> >>division_name | divisions_id >>-------------------------------------- >> DEC | 6 >> DEPI | 7 >> DRC | 8 >> >>How can I create a query that displays How the divisions answered >>the question please do assist. > >Martin - you'll need to explain exactly what you want. Can you show >what outputs you would like given the above data? > >-- > Richard Huxton > Archonet Ltd _________________________________________________________________ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail From dev@archonet.com Fri May 7 13:26:29 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 74A9DD1F2F2 for ; Fri, 7 May 2004 10:26:29 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 68595-05 for ; Fri, 7 May 2004 10:26:12 -0300 (ADT) Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net [194.217.242.89]) by svr1.postgresql.org (Postfix) with ESMTP id 1FE4FD1E176 for ; Fri, 7 May 2004 10:26:09 -0300 (ADT) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-31.mail.demon.net with esmtp (Exim 3.35 #1) id 1BM5MY-000B7K-0V; Fri, 07 May 2004 14:26:04 +0100 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id A7CE1174ED; Fri, 7 May 2004 14:25:48 +0100 (BST) Message-ID: <409B8E5F.6090408@archonet.com> Date: Fri, 07 May 2004 14:25:51 +0100 From: Richard Huxton User-Agent: Mozilla Thunderbird 0.6 (X11/20040502) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Martin Kuria Cc: olly@lfix.co.uk, middink@indo.net.id, pgsql-sql@postgresql.org Subject: Re: SUM() & GROUP BY References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/71 X-Sequence-Number: 17785 Martin Kuria wrote: > > Here is what Division table contains: > > SELECT * FROM ser_divisions; > > divisions_name | divisions_id > --------------------------------------- > DEE | 3131 ... > Now I would like to get to know how each Division answered i.e. > > SELECT s.pd_geo, COUNT(s.pd_geo) > FROM ser s > WHERE s.ser_divisions = '3131' > GROUP BY s.pd_geo; > divisions_name | pd_geo | count > ----------------------------------------------- > DEE | 1 | 9 > DEE | 2 | 2 > DEE | 3 | 6 > DEE | 4 | 5 SELECT d.divisions_name, s.pd_geo, COUNT(s.pd_geo) FROM ser_divisions d, ser s WHERE d.divisions_id = s.ser_divisions GROUP BY d.divisions_name, s.pd_geo ORDER BY d.divisions_name, s.pd_geo ; It's called a join, and any good SQL book should cover it. -- Richard Huxton Archonet Ltd From jllachan@sympatico.ca Fri May 7 17:31:13 2004 X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id ACCB8D1F49F for ; Fri, 7 May 2004 14:31:13 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 57508-09 for ; Fri, 7 May 2004 14:30:53 -0300 (ADT) Received: from simmts12-srv.bellnexxia.net (simmts12.bellnexxia.net [206.47.199.141]) by svr1.postgresql.org (Postfix) with ESMTP id 3FE4ED1ED74 for ; Fri, 7 May 2004 14:30:52 -0300 (ADT) Received: from localhost ([67.71.24.121]) by simmts12-srv.bellnexxia.net (InterMail vM.5.01.06.05 201-253-122-130-105-20030824) with ESMTP id <20040507173052.KNCI15788.simmts12-srv.bellnexxia.net@localhost>; Fri, 7 May 2004 13:30:52 -0400 Received: from sympatico.ca ([192.168.250.6]) by (8.11.6/8.11.6) with ESMTP id i47HUmg02303; Fri, 7 May 2004 13:30:48 -0400 Message-ID: <409BC7F8.5070309@sympatico.ca> Date: Fri, 07 May 2004 13:31:36 -0400 From: Jean-Luc Lachance User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Martin Kuria Cc: dev@archonet.com, olly@lfix.co.uk, middink@indo.net.id, pgsql-sql@postgresql.org Subject: Re: SUM() & GROUP BY References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/74 X-Sequence-Number: 17788 Try: SELECT d.divisions_name, s.pd_geo, COUNT(s.pd_geo) FROM ser s, ser_divisions d WHERE s.ser_divisions = '3131' AND s.ser_divisions = d.divisions_id GROUP BY d.divisions_name, s.pd_geo; Martin Kuria wrote: > Thanks Huxton, > > Sorry for not explaining fully here is what I would like to achieve: > > When I do: > > SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s GROUP BY s.pd_geo; > OUTPUT: > pd_geo | count > ---------------------- > 1 | 49 > 2 | 39 > 3 | 6 > 4 | 54 > > It outputs the number of entries the Divisions have made > > Here is what Division table contains: > > SELECT * FROM ser_divisions; > > divisions_name | divisions_id > --------------------------------------- > DEE | 3131 > DEPI | 3133 > DED | 3134 > GBH | 3136 > > Now I would like to get to know how each Division answered i.e. > > SELECT s.pd_geo, COUNT(s.pd_geo) > FROM ser s > WHERE s.ser_divisions = '3131' > GROUP BY s.pd_geo; > > output: > > pd_geo | count > ---------------------- > 1 | 9 > 2 | 2 > 3 | 6 > 4 | 5 > > But this is the output I intend to get: > > > divisions_name | pd_geo | count > ----------------------------------------------- > DEE | 1 | 9 > DEE | 2 | 2 > DEE | 3 | 6 > DEE | 4 | 5 > > How do I achieve the above results please do advice thanks again. > > Kind Regards > +-----------------------------------------------------+ > | Martin W. Kuria (Mr.) martin.kuria@unon.org > +----------------------------------------------------+ > > > > > >From: Richard Huxton <dev@archonet.com> > >To: Martin Kuria <martinkuria@hotmail.com> > >CC: olly@lfix.co.uk, middink@indo.net.id, pgsql-sql@postgresql.org > >Subject: Re: [SQL] SUM() & GROUP BY > >Date: Fri, 07 May 2004 09:00:43 +0100 > > > >Martin Kuria wrote: > >>Hi again I have two tables I would like to query i.e. service table > >>and division table > >> > >>SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s GROUP BY s.pd_geo; > >>OUTPUT: > >>pd_geo | count > >>---------------------- > >> 1 | 49 > >> 2 | 39 > >> 3 | 6 > >> 4 | 54 > >> > >>SELECT d.divisions_name, d.divisions_id) > >>FROM ser s, ser_divisions d > >>WHERE d.divisions_id = s.ser_divisions; > >> > >>division_name | divisions_id > >>-------------------------------------- > >> DEC | 6 > >> DEPI | 7 > >> DRC | 8 > >> > >>How can I create a query that displays How the divisions answered > >>the question please do assist. > > > >Martin - you'll need to explain exactly what you want. Can you show > >what outputs you would like given the above data? > > > >-- > > Richard Huxton > > Archonet Ltd > > _________________________________________________________________ > Tired of spam? Get advanced junk mail protection with MSN 8. > http://join.msn.com/?page=features/junkmail > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >