Thread: join/group/count query.

join/group/count query.

From
Gary Stainburn
Date:
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     


Re: join/group/count query.

From
Ragnar
Date:
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





Re: join/group/count query.

From
Erik Jones
Date:
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)



Re: join/group/count query.

From
Gary Stainburn
Date:
>
> 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     


Re: join/group/count query.

From
Erik Jones
Date:
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)



Re: join/group/count query.

From
"Hector Villarreal"
Date:
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


Re: join/group/count query.

From
Gary Stainburn
Date:
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