pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedjoin/group/count query.
7+ messages / 4 participants
[nested] [flat]
* join/group/count query.
@ 2006-12-20 10:12 Gary Stainburn <gary.stainburn@ringways.co.uk>
2006-12-20 11:09 ` Re: join/group/count query. Ragnar <gnari@hive.is>
0 siblings, 1 reply; 7+ messages in thread
From: Gary Stainburn @ 2006-12-20 10:12 UTC (permalink / raw)
To: pgsql-sql
Hi folks. I have the following query which works for me at the moment.
However, o_model refers to a table stock_models which has one record for each
model type. At the moment if I another record to the stock_models I have to
amend the select. Is it possible to make this automatic by joining the
stock_models data somehow?
select o_p_id as key, o_p_id as p_id, p_name, count(o_p_id) as total,
count (case when o_model = 5 then 1 else NULL end) as KA,
count (case when o_model = 10 then 1 else NULL end) as Focus,
count (case when o_model = 13 then 1 else NULL end) as C_Max,
count (case when o_model = 16 then 1 else NULL end) as S_Max,
count (case when o_model = 20 then 1 else NULL end) as Fiesta,
count (case when o_model = 25 then 1 else NULL end) as Fusion,
count (case when o_model = 30 then 1 else NULL end) as Mondeo,
count (case when o_model = 35 then 1 else NULL end) as Galaxy,
count (case when o_model = 40 then 1 else NULL end) as Ranger,
count (case when o_model = 50 then 1 else NULL end) as Connect,
count (case when o_model = 60 then 1 else NULL end) as Transit,
count (case when o_model = 70 then 1 else NULL end) as Fiesta_Van
from order_details
where date_trunc('month',CURRENT_DATE) = date_trunc('month',o_ord_date)
group by o_p_id, p_name;
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: join/group/count query.
2006-12-20 10:12 join/group/count query. Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2006-12-20 11:09 ` Ragnar <gnari@hive.is>
2006-12-20 15:46 ` Re: join/group/count query. Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 7+ messages in thread
From: Ragnar @ 2006-12-20 11:09 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
On mið, 2006-12-20 at 10:12 +0000, Gary Stainburn wrote:
> Hi folks. I have the following query which works for me at the moment.
> However, o_model refers to a table stock_models which has one record for each
> model type. At the moment if I another record to the stock_models I have to
> amend the select. Is it possible to make this automatic by joining the
> stock_models data somehow?
>
> select o_p_id as key, o_p_id as p_id, p_name, count(o_p_id) as total,
> count (case when o_model = 5 then 1 else NULL end) as KA,
> count (case when o_model = 10 then 1 else NULL end) as Focus,
> count (case when o_model = 13 then 1 else NULL end) as C_Max,
> count (case when o_model = 16 then 1 else NULL end) as S_Max,
> count (case when o_model = 20 then 1 else NULL end) as Fiesta,
> count (case when o_model = 25 then 1 else NULL end) as Fusion,
> count (case when o_model = 30 then 1 else NULL end) as Mondeo,
> count (case when o_model = 35 then 1 else NULL end) as Galaxy,
> count (case when o_model = 40 then 1 else NULL end) as Ranger,
> count (case when o_model = 50 then 1 else NULL end) as Connect,
> count (case when o_model = 60 then 1 else NULL end) as Transit,
> count (case when o_model = 70 then 1 else NULL end) as Fiesta_Van
> from order_details
> where date_trunc('month',CURRENT_DATE) = date_trunc('month',o_ord_date)
> group by o_p_id, p_name;
if I understand correctly, you want one column in your output,
for each row in the table table stock_models
you can do this with the crosstabN function in the contrib
module 'tablefunc', or by making your own procedural language
function.
gnari
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: join/group/count query.
2006-12-20 10:12 join/group/count query. Gary Stainburn <gary.stainburn@ringways.co.uk>
2006-12-20 11:09 ` Re: join/group/count query. Ragnar <gnari@hive.is>
@ 2006-12-20 15:46 ` Erik Jones <erik@myemma.com>
2006-12-20 16:20 ` Re: join/group/count query. Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 1 reply; 7+ messages in thread
From: Erik Jones @ 2006-12-20 15:46 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
Ragnar wrote:
> On mið, 2006-12-20 at 10:12 +0000, Gary Stainburn wrote:
>
>> Hi folks. I have the following query which works for me at the moment.
>> However, o_model refers to a table stock_models which has one record for each
>> model type. At the moment if I another record to the stock_models I have to
>> amend the select. Is it possible to make this automatic by joining the
>> stock_models data somehow?
>>
>> select o_p_id as key, o_p_id as p_id, p_name, count(o_p_id) as total,
>> count (case when o_model = 5 then 1 else NULL end) as KA,
>> count (case when o_model = 10 then 1 else NULL end) as Focus,
>> count (case when o_model = 13 then 1 else NULL end) as C_Max,
>> count (case when o_model = 16 then 1 else NULL end) as S_Max,
>> count (case when o_model = 20 then 1 else NULL end) as Fiesta,
>> count (case when o_model = 25 then 1 else NULL end) as Fusion,
>> count (case when o_model = 30 then 1 else NULL end) as Mondeo,
>> count (case when o_model = 35 then 1 else NULL end) as Galaxy,
>> count (case when o_model = 40 then 1 else NULL end) as Ranger,
>> count (case when o_model = 50 then 1 else NULL end) as Connect,
>> count (case when o_model = 60 then 1 else NULL end) as Transit,
>> count (case when o_model = 70 then 1 else NULL end) as Fiesta_Van
>> from order_details
>> where date_trunc('month',CURRENT_DATE) = date_trunc('month',o_ord_date)
>> group by o_p_id, p_name;
>>
>
> if I understand correctly, you want one column in your output,
> for each row in the table table stock_models
>
> you can do this with the crosstabN function in the contrib
> module 'tablefunc', or by making your own procedural language
> function.
>
> gnari
>
And, I may be missing something, but I'm having a hard time
understanding why you have all of those select columns of the form:
count (case when o_model = 5 then 1 else NULL end) as KA,
Considering that that can only return 1 or 0, the case statement would do. Is it to avoid putting all of the column names in the group by clause? That's hackish and is as much or more typing.
With regards to what you are actually trying to do, giving us your table definitions and what you are trying to achieve would help a lot more than just telling us the problem you are having. The column names in your query are in no way descriptive and tell us nothing about your actual table structure.
--
erik jones <erik@myemma.com>
software development
emma(r)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: join/group/count query.
2006-12-20 10:12 join/group/count query. Gary Stainburn <gary.stainburn@ringways.co.uk>
2006-12-20 11:09 ` Re: join/group/count query. Ragnar <gnari@hive.is>
2006-12-20 15:46 ` Re: join/group/count query. Erik Jones <erik@myemma.com>
@ 2006-12-20 16:20 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2006-12-20 16:32 ` Re: join/group/count query. Erik Jones <erik@myemma.com>
2006-12-20 18:06 ` Re: join/group/count query. Hector Villarreal <HVillarreal@mynewplace.com>
0 siblings, 2 replies; 7+ messages in thread
From: Gary Stainburn @ 2006-12-20 16:20 UTC (permalink / raw)
To: pgsql-sql
>
> And, I may be missing something, but I'm having a hard time
> understanding why you have all of those select columns of the form:
>
> count (case when o_model = 5 then 1 else NULL end) as KA,
>
> Considering that that can only return 1 or 0, the case statement would do.
> Is it to avoid putting all of the column names in the group by clause?
> That's hackish and is as much or more typing.
>
> With regards to what you are actually trying to do, giving us your table
> definitions and what you are trying to achieve would help a lot more than
> just telling us the problem you are having. The column names in your query
> are in no way descriptive and tell us nothing about your actual table
> structure.
The order_details view is a join of the orders table to the other tables.
The order table contains the orders.
Each order has a business partner which is in a separate table. p_id is the
key, p_name is the name of the partner.
Each order is for a single vehicle, which must be of a specific mode. The
models are also stored in a separate table. o_model is the attribute in the
orders table that contains the key to the models table.
The output I'm getting is below, which is what I want. For each partner I get
a total followed by a breakdown by model the orders for the current month.
key|p_id| p_name | total | ka | focus | c_max | s_max | fiesta |
fusion | mondeo | galaxy | ranger | connect | transit | fiesta_van
---+----+----------------+-------+----+-------+-------+-------+--------+--------+--------+--------+--------+---------+---------+------------
40| 40|rrrrrrrrrrrrrrr | 2 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 |0
30| 30|rrrrr Group Plc | 2 |0 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |0
78| 78|rrrrr rrrrrrrrr | 2 |0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 0 |0
46| 46|rrrrrrrrrrrr ) | 3 |0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 0 |0
3| 3|eeeee | 8 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 2 | 4 |0
9| 9|eee | 3 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 2 |0
12| 12|qqqqqqqqqqqq |13 |0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 5 | 6 |0
10| 10|rr Motor | 7 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 5 |0
34| 34|ffffff fffffff | 3 |0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 2 |0
102| 102|xxxxxxxx xxxxxxx| 1 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |0
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: join/group/count query.
2006-12-20 10:12 join/group/count query. Gary Stainburn <gary.stainburn@ringways.co.uk>
2006-12-20 11:09 ` Re: join/group/count query. Ragnar <gnari@hive.is>
2006-12-20 15:46 ` Re: join/group/count query. Erik Jones <erik@myemma.com>
2006-12-20 16:20 ` Re: join/group/count query. Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2006-12-20 16:32 ` Erik Jones <erik@myemma.com>
1 sibling, 0 replies; 7+ messages in thread
From: Erik Jones @ 2006-12-20 16:32 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; +Cc: pgsql-sql
Gary Stainburn wrote:
>> And, I may be missing something, but I'm having a hard time
>> understanding why you have all of those select columns of the form:
>>
>> count (case when o_model = 5 then 1 else NULL end) as KA,
>>
>> Considering that that can only return 1 or 0, the case statement would do.
>> Is it to avoid putting all of the column names in the group by clause?
>> That's hackish and is as much or more typing.
>>
>> With regards to what you are actually trying to do, giving us your table
>> definitions and what you are trying to achieve would help a lot more than
>> just telling us the problem you are having. The column names in your query
>> are in no way descriptive and tell us nothing about your actual table
>> structure.
>>
>
> The order_details view is a join of the orders table to the other tables.
>
> The order table contains the orders.
>
> Each order has a business partner which is in a separate table. p_id is the
> key, p_name is the name of the partner.
>
> Each order is for a single vehicle, which must be of a specific mode. The
> models are also stored in a separate table. o_model is the attribute in the
> orders table that contains the key to the models table.
>
> The output I'm getting is below, which is what I want. For each partner I get
> a total followed by a breakdown by model the orders for the current month.
>
> key|p_id| p_name | total | ka | focus | c_max | s_max | fiesta |
> fusion | mondeo | galaxy | ranger | connect | transit | fiesta_van
> ---+----+----------------+-------+----+-------+-------+-------+--------+--------+--------+--------+--------+---------+---------+------------
> 40| 40|rrrrrrrrrrrrrrr | 2 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 |0
> 30| 30|rrrrr Group Plc | 2 |0 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |0
> 78| 78|rrrrr rrrrrrrrr | 2 |0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 0 |0
> 46| 46|rrrrrrrrrrrr ) | 3 |0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 0 |0
> 3| 3|eeeee | 8 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 2 | 4 |0
> 9| 9|eee | 3 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 2 |0
> 12| 12|qqqqqqqqqqqq |13 |0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 5 | 6 |0
> 10| 10|rr Motor | 7 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 5 |0
> 34| 34|ffffff fffffff | 3 |0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 2 |0
> 102| 102|xxxxxxxx xxxxxxx| 1 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |0
>
Ok, then, yeah, I read those count statements wrong and the crosstab
contrib is what you're looking for.
--
erik jones <erik@myemma.com>
software development
emma(r)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: join/group/count query.
2006-12-20 10:12 join/group/count query. Gary Stainburn <gary.stainburn@ringways.co.uk>
2006-12-20 11:09 ` Re: join/group/count query. Ragnar <gnari@hive.is>
2006-12-20 15:46 ` Re: join/group/count query. Erik Jones <erik@myemma.com>
2006-12-20 16:20 ` Re: join/group/count query. Gary Stainburn <gary.stainburn@ringways.co.uk>
@ 2006-12-20 18:06 ` Hector Villarreal <HVillarreal@mynewplace.com>
2006-12-21 09:46 ` Re: join/group/count query. Gary Stainburn <gary.stainburn@ringways.co.uk>
1 sibling, 1 reply; 7+ messages in thread
From: Hector Villarreal @ 2006-12-20 18:06 UTC (permalink / raw)
To: Gary Stainburn <gary.stainburn@ringways.co.uk>; pgsql-sql
HI in reading this is it possible what was really intended :
Sum(case when o_model = 5 then 1 else NULL end) as KA
That would provide a count of all records meeting that condition.
Otherwise the count( approach will not do that.
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Gary Stainburn
Sent: Wednesday, December 20, 2006 8:20 AM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] join/group/count query.
>
> And, I may be missing something, but I'm having a hard time
> understanding why you have all of those select columns of the form:
>
> count (case when o_model = 5 then 1 else NULL end) as KA,
>
> Considering that that can only return 1 or 0, the case statement would
do.
> Is it to avoid putting all of the column names in the group by clause?
> That's hackish and is as much or more typing.
>
> With regards to what you are actually trying to do, giving us your
table
> definitions and what you are trying to achieve would help a lot more
than
> just telling us the problem you are having. The column names in your
query
> are in no way descriptive and tell us nothing about your actual table
> structure.
The order_details view is a join of the orders table to the other
tables.
The order table contains the orders.
Each order has a business partner which is in a separate table. p_id is
the
key, p_name is the name of the partner.
Each order is for a single vehicle, which must be of a specific mode.
The
models are also stored in a separate table. o_model is the attribute in
the
orders table that contains the key to the models table.
The output I'm getting is below, which is what I want. For each partner
I get
a total followed by a breakdown by model the orders for the current
month.
key|p_id| p_name | total | ka | focus | c_max | s_max | fiesta |
fusion | mondeo | galaxy | ranger | connect | transit | fiesta_van
---+----+----------------+-------+----+-------+-------+-------+--------+
--------+--------+--------+--------+---------+---------+------------
40| 40|rrrrrrrrrrrrrrr | 2 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1
|0
30| 30|rrrrr Group Plc | 2 |0 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
|0
78| 78|rrrrr rrrrrrrrr | 2 |0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 0
|0
46| 46|rrrrrrrrrrrr ) | 3 |0 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 1 | 0
|0
3| 3|eeeee | 8 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 2 | 4
|0
9| 9|eee | 3 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 2
|0
12| 12|qqqqqqqqqqqq |13 |0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 5 | 6
|0
10| 10|rr Motor | 7 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 5
|0
34| 34|ffffff fffffff | 3 |0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 2
|0
102| 102|xxxxxxxx xxxxxxx| 1 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 0 | 0
|0
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: join/group/count query.
2006-12-20 10:12 join/group/count query. Gary Stainburn <gary.stainburn@ringways.co.uk>
2006-12-20 11:09 ` Re: join/group/count query. Ragnar <gnari@hive.is>
2006-12-20 15:46 ` Re: join/group/count query. Erik Jones <erik@myemma.com>
2006-12-20 16:20 ` Re: join/group/count query. Gary Stainburn <gary.stainburn@ringways.co.uk>
2006-12-20 18:06 ` Re: join/group/count query. Hector Villarreal <HVillarreal@mynewplace.com>
@ 2006-12-21 09:46 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
0 siblings, 0 replies; 7+ messages in thread
From: Gary Stainburn @ 2006-12-21 09:46 UTC (permalink / raw)
To: pgsql-sql
On Wednesday 20 December 2006 18:06, Hector Villarreal wrote:
> HI in reading this is it possible what was really intended :
> Sum(case when o_model = 5 then 1 else NULL end) as KA
> That would provide a count of all records meeting that condition.
> Otherwise the count( approach will not do that.
>
Because the else is NULL, count does work. Although I would imagine sum will
also work, count more reflects the logic, i.e. the number of times that
condition is true
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2006-12-21 09:46 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-20 10:12 join/group/count query. Gary Stainburn <gary.stainburn@ringways.co.uk>
2006-12-20 11:09 ` Ragnar <gnari@hive.is>
2006-12-20 15:46 ` Erik Jones <erik@myemma.com>
2006-12-20 16:20 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2006-12-20 16:32 ` Erik Jones <erik@myemma.com>
2006-12-20 18:06 ` Hector Villarreal <HVillarreal@mynewplace.com>
2006-12-21 09:46 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
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