Re: SUM() & GROUP BY

From: "Muhyiddin A(dot)M Hayat" <middink(at)indo(dot)net(dot)id>
To: <pgsql-sql(at)postgresql(dot)org>
Cc: "Oliver Elphick" <olly(at)lfix(dot)co(dot)uk>
Subject: Re: SUM() & GROUP BY
Date: 2003-09-29 01:22:04
Message-ID: 006701c38628$17dba3f0$30179fca@middinkcomp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


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(at)lfix(dot)co(dot)uk>
To: "Muhyiddin A.M Hayat" <middink(at)indo(dot)net(dot)id>
Cc: <pgsql-sql(at)postgresql(dot)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(dot)Elphick(at)lfix(dot)co(dot)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
>

Attachment Content-Type Size
billing.sql application/octet-stream 5.6 KB

Browse pgsql-sql by date

  From Date Subject
Next Message vijaykumar M 2003-09-29 05:16:49 Re: Temporary tables
Previous Message Muhyiddin A.M Hayat 2003-09-29 01:20:04 Re: SUM() & GROUP BY