Re: SUM() & GROUP BY

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
>