Re: join/group/count query.
От | Erik Jones |
---|---|
Тема | Re: join/group/count query. |
Дата | |
Msg-id | 45896580.5090500@myemma.com обсуждение исходный текст |
Ответ на | Re: join/group/count query. (Gary Stainburn <gary.stainburn@ringways.co.uk>) |
Список | 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)
В списке pgsql-sql по дате отправления: