Re: SUM() & GROUP BY

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
>