Thread: SUM() & GROUP BY

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
shallreceive the crown of life, which     the Lord hath promised to them that love him."
                James 1:12 
 

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" <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
>

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

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

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

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

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
>