From: Muhyiddin A.M Hayat <middink@indo.net.id>
To: pgsql-sql@postgresql.org
Cc: Oliver Elphick <olly@lfix.co.uk>
Subject: Re: SUM() & GROUP BY
Date: Mon, 29 Sep 2003 09:22:04 +0800
Message-ID: <006701c38628$17dba3f0$30179fca@middinkcomp> (raw)
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" <olly@lfix.co.uk>
To: "Muhyiddin A.M Hayat" <middink@indo.net.id>
Cc: <pgsql-sql@postgresql.org>
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
>
Attachments:
billing.sql (application/octet-stream, 5.6K) download
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: middink@indo.net.id, olly@lfix.co.uk
Subject: Re: SUM() & GROUP BY
In-Reply-To: <006701c38628$17dba3f0$30179fca@middinkcomp>
* 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