pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSUM() & GROUP BY
9+ messages / 5 participants
[nested] [flat]
* SUM() & GROUP BY
@ 2003-09-28 18:01 Muhyiddin A.M Hayat <middink@indo.net.id>
0 siblings, 1 reply; 9+ messages in thread
From: Muhyiddin A.M Hayat @ 2003-09-28 18:01 UTC (permalink / raw)
To: pgsql-sql
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 ??
How to using SUM() & GROUP BY
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SUM() & GROUP BY
@ 2003-09-28 19:56 Oliver Elphick <olly@lfix.co.uk>
parent: Muhyiddin A.M Hayat <middink@indo.net.id>
0 siblings, 1 reply; 9+ messages in thread
From: Oliver Elphick @ 2003-09-28 19:56 UTC (permalink / raw)
To: Muhyiddin A.M Hayat <middink@indo.net.id>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SUM() & GROUP BY
@ 2003-09-29 01:20 Muhyiddin A.M Hayat <middink@indo.net.id>
parent: Oliver Elphick <olly@lfix.co.uk>
0 siblings, 0 replies; 9+ messages in thread
From: Muhyiddin A.M Hayat @ 2003-09-29 01:20 UTC (permalink / raw)
To: pgsql-sql; +Cc: Oliver Elphick <olly@lfix.co.uk>
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
>
Attachments:
billing.sql (application/octet-stream, 5.6K) download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SUM() & GROUP BY
@ 2003-09-29 01:22 Muhyiddin A.M Hayat <middink@indo.net.id>
0 siblings, 0 replies; 9+ messages in thread
From: Muhyiddin A.M Hayat @ 2003-09-29 01:22 UTC (permalink / raw)
To: pgsql-sql; +Cc: Oliver Elphick <olly@lfix.co.uk>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SUM() & GROUP BY
@ 2004-05-07 06:47 Martin Kuria <martinkuria@hotmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Martin Kuria @ 2004-05-07 06:47 UTC (permalink / raw)
To: olly@lfix.co.uk; middink@indo.net.id; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SUM() & GROUP BY
@ 2004-05-07 08:00 Richard Huxton <dev@archonet.com>
parent: Martin Kuria <martinkuria@hotmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Richard Huxton @ 2004-05-07 08:00 UTC (permalink / raw)
To: Martin Kuria <martinkuria@hotmail.com>; +Cc: olly@lfix.co.uk; middink@indo.net.id; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SUM() & GROUP BY
@ 2004-05-07 11:32 Martin Kuria <martinkuria@hotmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Martin Kuria @ 2004-05-07 11:32 UTC (permalink / raw)
To: dev@archonet.com; +Cc: olly@lfix.co.uk; middink@indo.net.id; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SUM() & GROUP BY
@ 2004-05-07 13:25 Richard Huxton <dev@archonet.com>
parent: Martin Kuria <martinkuria@hotmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Richard Huxton @ 2004-05-07 13:25 UTC (permalink / raw)
To: Martin Kuria <martinkuria@hotmail.com>; +Cc: olly@lfix.co.uk; middink@indo.net.id; pgsql-sql
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: SUM() & GROUP BY
@ 2004-05-07 17:31 Jean-Luc Lachance <jllachan@sympatico.ca>
parent: Martin Kuria <martinkuria@hotmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Jean-Luc Lachance @ 2004-05-07 17:31 UTC (permalink / raw)
To: Martin Kuria <martinkuria@hotmail.com>; +Cc: dev@archonet.com; olly@lfix.co.uk; middink@indo.net.id; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2004-05-07 17:31 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-28 18:01 SUM() & GROUP BY Muhyiddin A.M Hayat <middink@indo.net.id>
2003-09-28 19:56 ` Oliver Elphick <olly@lfix.co.uk>
2003-09-29 01:20 ` Muhyiddin A.M Hayat <middink@indo.net.id>
2003-09-29 01:22 Re: SUM() & GROUP BY Muhyiddin A.M Hayat <middink@indo.net.id>
2004-05-07 06:47 Re: SUM() & GROUP BY Martin Kuria <martinkuria@hotmail.com>
2004-05-07 08:00 ` Richard Huxton <dev@archonet.com>
2004-05-07 11:32 Re: SUM() & GROUP BY Martin Kuria <martinkuria@hotmail.com>
2004-05-07 13:25 ` Richard Huxton <dev@archonet.com>
2004-05-07 17:31 ` Jean-Luc Lachance <jllachan@sympatico.ca>
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