Thread: join/group/count query.
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
NULLend) 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
elseNULL end) as Fiesta, count (case when o_model = 25 then 1 else NULL end) as Fusion, count (case when o_model = 30
then1 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
(casewhen 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
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
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
namesin 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
wouldhelp a lot more than just telling us the problem you are having. The column names in your query are in no way
descriptiveand tell us nothing about your actual table structure.
--
erik jones <erik@myemma.com>
software development
emma(r)
> > 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 |030| 30|rrrrr Group Plc | 2 |0 | 2 | 0 | 0 | 0 | 0 |0 | 0 | 0 | 0 | 0 |078| 78|rrrrr rrrrrrrrr | 2 |0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 0 |046| 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 |012| 12|qqqqqqqqqqqq |13 |0 | 0 | 0 | 0 | 0 | 0| 2 | 0 | 0 | 5 | 6 |010| 10|rr Motor | 7 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 5 |034| 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
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)
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
|030| 30|rrrrr Group Plc | 2 |0 | 2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
|078| 78|rrrrr rrrrrrrrr | 2 |0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | 0
|046| 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
|012| 12|qqqqqqqqqqqq |13 |0 | 0 | 0 | 0 | 0 | 0 | 2 | 0 | 0 | 5 | 6
|010| 10|rr Motor | 7 |0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 5
|034| 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
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