[SQL] contrib/tablefunc crosstab
Hi,
I am using the crosstab function by Joe Conway and I am having a problem
that could be my fault but I'm not sure.
My query/result is:
atc=# SELECT * FROM crosstab('SELECT cli_tran_trans_date,
TRIM(dat_staff_code) AS dat_staff_code, SUM(cli_tran_amount) from vetpmardet
WHERE cli_tran_trans_date BETWEEN ''2003-06-01'' AND ''2003-06-05'' AND
TRIM(dat_staff_code) NOT LIKE GROUP BY cli_tran_trans_date,
dat_staff_code ORDER BY 1,2;',5) AS vetpmardet(date date, vet1 numeric, vet2
numeric, vet3 numeric, vet4 numeric, vet5 numeric);
date| vet1 | vet2 | vet3 | vet4 | vet5
++-+-++
2003-06-01 | 561.99 | | ||
2003-06-02 | 418.43 | 548.10 | 790.16 | 54.24 | 467.07
2003-06-03 | 518.62 | 2115.57 | 630.83 | 195.74 | 548.26
2003-06-04 | 870.52 | 346.49 | 393.77 | 470.02 |
2003-06-05 | 74.37 | 3555.10 | 1248.69 | 758.18 |
(5 rows)
My problem is that the rows are populated from right to left irrespective of
the column. So the $561.99 in vet1 actually belongs to vet2. There are never
any blank cells in the middle or start of a row - they always come at the
end. How do I get the amount for that date into the appropriate column?
I am using 7.4.3
Caleb
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] contrib/tablefunc crosstab
Caleb Simonyi-Gindele wrote: My problem is that the rows are populated from right to left irrespective of the column. So the $561.99 in vet1 actually belongs to vet2. There are never any blank cells in the middle or start of a row - they always come at the end. How do I get the amount for that date into the appropriate column? You either need to be sure there are no "gaps" in your source rows (provide one row for every category for each group, e.g. by doing an outer join) or use the hashed version of the function. Search the README for: crosstab(text source_sql, text category_sql) That is the hashed version that will do what you're looking for. HTH, Joe ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Sum and count weird results
Hi all, I have these tables a ( id integer, email varchar); b ( seat varchar, transactionid varchar); c ( transactionid varchar, totalprice numeric(8.2)); ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Trigger and function not on speaking terms
You need to define fn_foo w/o params per Doc Section 19.9.
Your intent as expressed in the trigger def (args) can then be fulfilled
through special top level vars.
On Mon, 2004-08-02 at 16:20, Jeff Boes wrote:
> Hmm, this is puzzling me:
>
> create or replace function fn_foo(text) returns trigger as '
> begin
># Do some stuff with $1
> end;
> ' language 'plpgsql';
>
> CREATE FUNCTION
>
> create table bar (aaa text);
>
> CREATE TABLE
>
> create trigger trg_bar
> after insert or update on bar
> execute procedure fn_foo('string');
>
> ERROR: function fn_foo() does not exist
>
> It would seem my trigger definition is trying to find fn_foo(), when I
> mean for it to call fn_foo(TEXT).
---(end of broadcast)---
TIP 8: explain analyze is your friend
[SQL] SQL syntax extentions - to put postgres ahead in the race
Hi, I am a developer working with many databases, as a part of my job. I use heavy SQL queries and have become somewhat of an expert in SQL, including tricks and workarounds of the limitation of the language. I feel like a lot of the workarounds could be avoided with adding a few new operators to the SQL language that should be rather easy to support but would give a vast improvement and probably a leap of performance in many common queries. I write you about this hoping that you would support these operators (even though they are not in the ANSI) and thereby position PostGres as a leader and not just a follower! I personaly have a great interest in seeing open source software making it big time! So please - forward this to one of your more technical developers/executives and hopefully I will get to see it in the next version of PostGres: 1) The operator "of max":suppose I have a table "grades" of 3 fields: class/student/grade where I store many grades of many students of many classes. I want to get the name of the highest scoring student in each class. Note that there may be many students with the same grade, but for starters let's say there is a primary key of class+grade. My query would now be:select student from grades where class+'#'+grade in ( select class+'#'+max(grade) from grades group by class) a This means working the query twice - and relying on louzy conversion of the grade from numerical to textual.We could also use: select student from grades where student in ( select student from grades group by class having class+'#'+grade = max(class+'#'+grade)) a This is even worse! The optimal would be to introduce a new operator "of max" that would be used as follows: select student of max(grade) from grades group by class simillarly one should support "of min" and "of any" (brings a representative of the group) 2) aggregated concatenation:Traditionally the SQL language has referred from supporting order dependent operators from taking a role in aggregated functions. This means that since the query: "select class, grade from grades" does not ensure the order of the records returned, the operation sum() is supported (i.e. select class, sum(grade) from grades group by class) but other operations that would be order dependent are not supported.I think this approach should be revised. In many cases one would want to get a list of the student names delimited with a comma. It would be great if one could write:select class, list(student, ',') from grades group by classand get class list- class1 john, ruth,.. This is of course an order dependent operation so the syntax can either be:select class, list(student, ',') from grades group by class order by student, gradein which case the list would be ordered before the list is created, or if no particular order is requested the concatenation of the names should be in an arbitrary order. Well - that's all for now :-) Good luck! Ram
Re: [SQL] SQL syntax extentions - to put postgres ahead in the race
On Thu, 5 Aug 2004, Ram Nathaniel wrote: > > 1) The operator "of max": > suppose I have a table "grades" of 3 fields: class/student/grade where I > store many grades of many students of many classes. I want to get the > name of the highest scoring student in each class. Note that there may > be many students with the same grade, but for starters let's say there > is a primary key of class+grade. > > My query would now be: > select student from grades where class+'#'+grade in > ( >select class+'#'+max(grade) from grades group by class > ) a As a side note, I'd think that something like: select student from grades where (class,grade) in (select class, max(grade) from grades group by class); should avoid textual operations. I'm assuming the + above are meant to be concatenation (||). > The optimal would be to introduce a new operator "of max" that would be used as > follows: > > select student of max(grade) from grades group by class PostgreSQL provides an extension called DISTINCT ON. Something like select distinct on (class) student from grades order by class, grade desc; should get you one arbitrary student with the highest grade in his or her class. If you want to order by the grades, I think you need a layer around it. If you don't care about the class order, you might consider making the class ordering desc as well to make it easier to use a multi-column index on (class,grade). > 2) aggregated concatenation: Theoretically, you should be able to do this right now in PostgreSQL with user defined aggregates (although you can't pass a second argument currently for the separator). I believe that an ordered subquery in FROM will currently allow you to get an ordered aggregate, or perhaps you'd have to turn off hash aggregation, but I think you should be able to get it to keep the ordering. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
