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 <[EMAIL PROTECTED]>
software development
emma(r)
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
