[SQL] cross tables, SELECT expressions, and GROUP BY problem
Hi,
I'm relatively stretched when it comes to SQL but I'm getting there. I
have a question though:
Thanks to various web sites I've succeeded in creating simple cross
tables in PostgreSQL. I'm now trying more complicated examples
(migrating queries from MS Access to PostgreSQL). I'm stuck on getting
grouping to work where the selection element isn't a real table field,
where it is generated by a CASE statement. Here's a full actual SELECT
statement, that works but isn't quite what I need, followed by the first
few rows of output:
SELECT DISTINCT
CASE
WHEN lower(c."Order") = 'coleoptera' THEN 5
WHEN lower(c."Order") = 'trichoptera' THEN 8
WHEN lower(c."Order") = 'ephemeroptera' THEN 6
WHEN lower(c."Order") = 'plecoptera' THEN 7
WHEN lower(c."Class") = 'oligochaeta' THEN 1
WHEN lower(c."Family") LIKE 'chiron%' THEN 2
WHEN lower(c."Order") = 'diptera' AND lower(c."Family") NOT
LIKE 'chiron%' THEN 3
ELSE 4
END AS "Ranking",
CASE
WHEN lower(c."Order") = 'coleoptera' THEN 'Coleoptera'
WHEN lower(c."Order") = 'trichoptera' THEN 'Trichoptera'
WHEN lower(c."Order") = 'ephemeroptera' THEN 'Ephemeroptera'
WHEN lower(c."Order") = 'plecoptera' THEN 'Plecoptera'
WHEN lower(c."Class") = 'oligochaeta' THEN 'Oligochaeta'
WHEN lower(c."Family") LIKE 'chiron%' THEN 'Chironomidae'
WHEN lower(c."Order") = 'diptera' AND lower(c."Family") NOT
LIKE 'chiron%' THEN 'Diptera (Other)'
ELSE 'Other'
END AS "Taxa",
SUM(CASE WHEN b."LocationCode" = '011' THEN c."Count" END) AS
"011",
SUM(CASE WHEN b."LocationCode" = '012' THEN c."Count" END) AS
"012",
SUM(CASE WHEN b."LocationCode" = '013' THEN c."Count" END) AS
"013",
SUM(CASE WHEN b."LocationCode" = '014' THEN c."Count" END) AS
"014"
FROM "tblBugIDSheetInfo" b
INNER JOIN "tblBugCount" c USING ("BugSheetID")
GROUP BY c."Order", c."Class", c."Family"
ORDER BY "Ranking"
Generates the following output:
Ranking Taxa011 012 013 014
---
1 "Oligochaeta" 487 171117591078
1 "Oligochaeta" 7
1 "Oligochaeta"
2 "Chironomidae" 1385233515001513
2 "Chironomidae"
3 "Diptera (Other)" 5
3 "Diptera (Other)" 1 1 3
3 "Diptera (Other)" 199 19 40 37
3 "Diptera (Other)"
...
I want to GROUP on the "Ranking" field as well so that all rows with the
same "Ranking" value are SUMmed. That is, I'm trying to achieve the
following:
Ranking Taxa011 012 013 014
---
1 "Oligochaeta" 494 171117591078
2 "Chironomidae" 1385233515001513
3 "Diptera (Other)" 199 20 41 45
...
I tried including "Ranking" at the head of the GROUP BY list, but it has
no effect.
Can anyone show me what I'm doing wrong and/or show me what I need to
change? I'd rather avoid wrapping the whole query in another SELECT that
would duplicate this one, assuming that that would work.
Thanks.
Ross Johnson
PS. I just want to acknowledge the very useful web sites that I found
via google which helped out considerably getting me this far with cross-
tables. The above query is based on this one:
http://dev.mysql.com/tech-resources/articles/wizard/print_version.html
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] cross tables, SELECT expressions, and GROUP BY problem
On Sun, 2006-04-02 at 16:43 +1000, Ross Johnson wrote:
> Hi,
>
> I'm relatively stretched when it comes to SQL but I'm getting there. I
> have a question though:
>
> Thanks to various web sites I've succeeded in creating simple cross
> tables in PostgreSQL. I'm now trying more complicated examples
> (migrating queries from MS Access to PostgreSQL). I'm stuck on getting
> grouping to work where the selection element isn't a real table field,
> where it is generated by a CASE statement. Here's a full actual SELECT
> statement, that works but isn't quite what I need, followed by the first
> few rows of output:
>
> SELECT DISTINCT
> CASE
> WHEN lower(c."Order") = 'coleoptera' THEN 5
> WHEN lower(c."Order") = 'trichoptera' THEN 8
> WHEN lower(c."Order") = 'ephemeroptera' THEN 6
> WHEN lower(c."Order") = 'plecoptera' THEN 7
> WHEN lower(c."Class") = 'oligochaeta' THEN 1
> WHEN lower(c."Family") LIKE 'chiron%' THEN 2
> WHEN lower(c."Order") = 'diptera' AND lower(c."Family") NOT
> LIKE 'chiron%' THEN 3
> ELSE 4
> END AS "Ranking",
> CASE
> WHEN lower(c."Order") = 'coleoptera' THEN 'Coleoptera'
> WHEN lower(c."Order") = 'trichoptera' THEN 'Trichoptera'
> WHEN lower(c."Order") = 'ephemeroptera' THEN 'Ephemeroptera'
> WHEN lower(c."Order") = 'plecoptera' THEN 'Plecoptera'
> WHEN lower(c."Class") = 'oligochaeta' THEN 'Oligochaeta'
> WHEN lower(c."Family") LIKE 'chiron%' THEN 'Chironomidae'
> WHEN lower(c."Order") = 'diptera' AND lower(c."Family") NOT
> LIKE 'chiron%' THEN 'Diptera (Other)'
> ELSE 'Other'
> END AS "Taxa",
> SUM(CASE WHEN b."LocationCode" = '011' THEN c."Count" END) AS
> "011",
> SUM(CASE WHEN b."LocationCode" = '012' THEN c."Count" END) AS
> "012",
> SUM(CASE WHEN b."LocationCode" = '013' THEN c."Count" END) AS
> "013",
> SUM(CASE WHEN b."LocationCode" = '014' THEN c."Count" END) AS
> "014"
> FROM "tblBugIDSheetInfo" b
> INNER JOIN "tblBugCount" c USING ("BugSheetID")
> GROUP BY c."Order", c."Class", c."Family"
> ORDER BY "Ranking"
>
> Generates the following output:
>
> Ranking Taxa011 012 013 014
> ---
> 1 "Oligochaeta" 487 171117591078
> 1 "Oligochaeta" 7
> 1 "Oligochaeta"
> 2 "Chironomidae" 1385233515001513
> 2 "Chironomidae"
> 3 "Diptera (Other)" 5
> 3 "Diptera (Other)" 1 1 3
> 3 "Diptera (Other)" 199 19 40 37
> 3 "Diptera (Other)"
> ...
I should add that I've just tried the following query, which is just a
rearrangement of the above query using a sub-SELECT, and the result is
the same as above apart from a slightly different ordering of the rows.
That is, GROUP BY "Ranking" still doesn't appear to do anything. I was
under the impression that a sub-SELECT creates a temporary, or at least
a pseudo-temporary table (a "virtual" table as it's called in the
documentation), so I would have expected this query to work even if the
above one doesn't.
SELECT DISTINCT
"Ranking", "Taxa",
SUM(CASE WHEN b."LocationCode" = '011' THEN c."Count" END) AS
"011",
SUM(CASE WHEN b."LocationCode" = '012' THEN c."Count" END) AS
"012",
SUM(CASE WHEN b."LocationCode" = '013' THEN c."Count" END) AS
"013",
SUM(CASE WHEN b.&qu
Re: [SQL] cross tables, SELECT expressions, and GROUP BY problem
Following up my own question again, I've realised my error and solved my
problem - in the interests of completing this thread, the working query
(two versions) can be found below...
On Sun, 2006-04-02 at 18:00 +1000, Ross Johnson wrote:
> On Sun, 2006-04-02 at 16:43 +1000, Ross Johnson wrote:
> > Hi,
> >
> > I'm relatively stretched when it comes to SQL but I'm getting there. I
> > have a question though:
> >
> > Thanks to various web sites I've succeeded in creating simple cross
> > tables in PostgreSQL. I'm now trying more complicated examples
> > (migrating queries from MS Access to PostgreSQL). I'm stuck on getting
> > grouping to work where the selection element isn't a real table field,
> > where it is generated by a CASE statement. Here's a full actual SELECT
> > statement, that works but isn't quite what I need, followed by the first
> > few rows of output:
> >
> > SELECT DISTINCT
> > CASE
> > WHEN lower(c."Order") = 'coleoptera' THEN 5
> > WHEN lower(c."Order") = 'trichoptera' THEN 8
> > WHEN lower(c."Order") = 'ephemeroptera' THEN 6
> > WHEN lower(c."Order") = 'plecoptera' THEN 7
> > WHEN lower(c."Class") = 'oligochaeta' THEN 1
> > WHEN lower(c."Family") LIKE 'chiron%' THEN 2
> > WHEN lower(c."Order") = 'diptera' AND lower(c."Family") NOT
> > LIKE 'chiron%' THEN 3
> > ELSE 4
> > END AS "Ranking",
> > CASE
> > WHEN lower(c."Order") = 'coleoptera' THEN 'Coleoptera'
> > WHEN lower(c."Order") = 'trichoptera' THEN 'Trichoptera'
> > WHEN lower(c."Order") = 'ephemeroptera' THEN 'Ephemeroptera'
> > WHEN lower(c."Order") = 'plecoptera' THEN 'Plecoptera'
> > WHEN lower(c."Class") = 'oligochaeta' THEN 'Oligochaeta'
> > WHEN lower(c."Family") LIKE 'chiron%' THEN 'Chironomidae'
> > WHEN lower(c."Order") = 'diptera' AND lower(c."Family") NOT
> > LIKE 'chiron%' THEN 'Diptera (Other)'
> > ELSE 'Other'
> > END AS "Taxa",
> > SUM(CASE WHEN b."LocationCode" = '011' THEN c."Count" END) AS
> > "011",
> > SUM(CASE WHEN b."LocationCode" = '012' THEN c."Count" END) AS
> > "012",
> > SUM(CASE WHEN b."LocationCode" = '013' THEN c."Count" END) AS
> > "013",
> > SUM(CASE WHEN b."LocationCode" = '014' THEN c."Count" END) AS
> > "014"
> > FROM "tblBugIDSheetInfo" b
> > INNER JOIN "tblBugCount" c USING ("BugSheetID")
> > GROUP BY c."Order", c."Class", c."Family"
> > ORDER BY "Ranking"
> >
> > Generates the following output:
> >
> > Ranking Taxa011 012 013 014
> > ---
> > 1 "Oligochaeta" 487 171117591078
> > 1 "Oligochaeta" 7
> > 1 "Oligochaeta"
> > 2 "Chironomidae" 1385233515001513
> > 2 "Chironomidae"
> > 3 "Diptera (Other)" 5
> > 3 "Diptera (Other)" 1 1 3
> > 3 "Diptera (Other)" 199 19 40 37
> > 3 "Diptera (Other)"
> > ...
>
> I should add that I've just tried the following query, which is just a
> rearrangement of the above query using a sub-SELECT, and the result is
> the same as above apart from a slightly different ordering of the rows.
> That is, GROUP BY "Ranking" still doesn't appear to do anything. I was
> under the impression that a sub-SELECT creates a temporary, or at least
> a pseudo-temporary table (a "virtual" table as it's called in the
> documentation), so I would have ex
Re: [SQL] concatenation with a null column (using ||) nulls the
On Mon, 2006-04-10 at 14:36 -0700, Neil Harkins wrote: > inventory=> SELECT cabinets_name, cabinets_description > FROM cabinets WHERE cabinets_datacenters = 2; > cabinets_name | cabinets_description > ---+-- > 548-4th-Cab2 | > 548-4th-RR1 | > 548-4th-RR2 | > 548-4th-Cab1 | > (4 rows) > > inventory=> SELECT cabinets_name || ' - ' || cabinets_description AS concat > FROM cabinets WHERE cabinets_datacenters = 2; > concat > - > > > > 548-4th-Cab1 - > (4 rows) > > Note: The cabinets_description for the "548-4th-Cab1" row is " ", > not NULL, hence it being displayed. Is this standard SQL behavior? > I don't know if this is the "correct SQL" answer, however, in the past, I've used the COALESCE() function to handle this situation. COALESCE(value [, ...]) The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. This is often useful to substitute a default value for null values when data is retrieved for display, for example: SELECT COALESCE(description, short_description, '(none)') ... Like a CASE expression, COALESCE will not evaluate arguments that are not needed to determine the result; that is, arguments to the right of the first non-null argument are not evaluated. Regards. > Client is from rpm: postgresql-8.0.7-1.FC4.1 > Server is from rpm: postgresql-server-8.0.7-1.FC4.1 > > -neil > > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] how to use recursion to find end nodes of a tree
On Mon, 2006-04-10 at 16:09 +0100, [EMAIL PROTECTED] wrote: > Hello All, > > I have been having a really hard time trying to come up with a pl/pgsql > recursive function to returns the end nodes of a tree. > Here is an example table definition: > > CREATE TABLE parent_child ( > parent_id integer NOT NULL, > child_id integer NOT NULL > ); > > INSERT INTO parent_child (parent_id, child_id) VALUES (1, 2); > INSERT INTO parent_child (parent_id, child_id) VALUES (1, 3); > INSERT INTO parent_child (parent_id, child_id) VALUES (1, 4); > INSERT INTO parent_child (parent_id, child_id) VALUES (2, 5); > INSERT INTO parent_child (parent_id, child_id) VALUES (2, 6); > INSERT INTO parent_child (parent_id, child_id) VALUES (4, 7); > INSERT INTO parent_child (parent_id, child_id) VALUES (4, 8); > INSERT INTO parent_child (parent_id, child_id) VALUES (4, 9); > INSERT INTO parent_child (parent_id, child_id) VALUES (9, 10); > What you appear to have is really this, with a missing first node: CREATE TABLE parent_child ( parent_id integer NOT NULL, this_node_id integer NULL, ); INSERT INTO parent_child (parent_id, this_node_id) VALUES (0, 1); INSERT INTO parent_child (parent_id, this_node_id) VALUES (1, 2); INSERT INTO parent_child (parent_id, this_node_id) VALUES (1, 3); INSERT INTO parent_child (parent_id, this_node_id) VALUES (1, 4); INSERT INTO parent_child (parent_id, this_node_id) VALUES (2, 5); INSERT INTO parent_child (parent_id, this_node_id) VALUES (2, 6); INSERT INTO parent_child (parent_id, this_node_id) VALUES (4, 7); INSERT INTO parent_child (parent_id, this_node_id) VALUES (4, 8); INSERT INTO parent_child (parent_id, this_node_id) VALUES (4, 9); INSERT INTO parent_child (parent_id, this_node_id) VALUES (9, 10); This makes it easy to search from leaf to root, but not from root to leaf. Without a list of child_ids in each node you must search the whole table for nodes that have the current node id as their parent_id in order to determine if a node is a leaf node or not. Perhaps you can include a child_id[] in each node, or a has_children boolean flag that you set and unset when inserting or deleting rows. But perhaps you can get PostgreSQL to do it for you by setting this_node_id as primary key and parent_id as foreign key referencing this same table. You could then test if it's a leaf node by attempting to change the node's this_node_id to some out-of-range value and see if it produces as error. If no error then it's a leaf node, (then you must restore this_node_id - I would try just negating it for the test, so I don't have to actually store the original value somewhere). > This produces the following tree of data: > >1 > ___|___ >| | | >2 3 4 > _|_ _|_ > | | | | | > 5 6 7 8 9 > | > 10 > > I want to create a function that returns the terminating nodes of > of this tree below a certain level i.e. if I input 1 to the function > I need it to return 5,6,3,7,8,10. If I input 4 to the function I would > get 7,8,10. I have written recursive functions which return all nodes > on a branch of a tree but I can't think of a way to return the end nodes > does anyone know of a solution? > > Many thanks, > > Mike > > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] set return function is returning a single record,
On Fri, 2006-04-28 at 12:56 +0200, A. Kretschmer wrote: > am 28.04.2006, um 16:14:10 +0530 mailte Penchalaiah P. folgendes: > > 4) The following is the function that retrieves the records from pss : > > > > CREATE or replace FUNCTION ftoc9() RETURNS setof structrankmaster2 > > LANGUAGE 'plpgsql' > > > > AS' DECLARE > > rowdata pss%rowtype; > > BEGIN for i in 1..3 loop > > select * into rowdata from pss ; > > return next rowdata ; > > end loop; > > return; > > end'; > > Your loop is wrong, for i in 1..3 select... and then returns the first > record. > > > Change this to: > > BEGIN > .. > for rowdata in select * from pss ; > return next rowdata ; > end loop; > .. > END; > > *untestet* If you meant to return the first 3 records, then: ... begin for rowdata in select * from pss limit 3 loop return next rowdata ; end loop; return; end'; You can also return a SETOF pss without creating the structrankmaster2 type. If this is actually all you are after, and not just a simplified example then you could also use this (also not tested): CREATE FUNCTION ftoc9() RETURNS SETOF pss AS $$ SELECT * FROM pss LIMIT 3; $$ LANGUAGE SQL; If you do use LIMIT, then ORDER BY might also be needed as well. Ross ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Multi-table insert using RULE - how to handle id?
Aaron Bono wrote:
On 7/19/06, *Collin Peters* <[EMAIL PROTECTED]
<mailto:[EMAIL PROTECTED]>> wrote:
I am learning about how to use rules to handle a multi-table insert.
Right now I have a user_activity table which tracks history and a
user_activity_users table which tracks what users are associated with
a row in user_activity (one to many relationship).
I created a rule (and a view called user_activity_single) which is to
simplify the case of inserting a row in user_activity in which there
is only one user in user_activity_users.
CREATE OR REPLACE RULE user_activity_single_insert AS
ON INSERT TO user_activity_single
DO INSTEAD (
INSERT INTO user_activity(
user_activity_id,
description,
...
)
VALUES (
NEW.user_activity_id,
NEW.description,
...
);
INSERT INTO user_activity_users (
user_activity_id,
user_id
)
VALUES (
NEW.user_activity_id,
NEW.user_id
);
);
This works well by itself, but the problem is that I have to manually
pass in the user_activity_id which is the primary key. I do this by
calling nextval to get the next ID in the sequence.
Is there any way to have the rule handle the primary key so I don't
have to pass it in? It seems you can't use pgsql inside the rule at
all. What I'm looking for is something like:
CREATE OR REPLACE RULE user_activity_single_insert AS
ON INSERT TO user_activity_single
DO INSTEAD (
SELECT nextval('user_activity_user_activity_id_seq') INTO
next_id;
INSERT INTO user_activity(
user_activity_id,
description,
...
)
VALUES (
next_id,
NEW.description,
...
);
INSERT INTO user_activity_users (
user_activity_id,
user_id
)
VALUES (
next_id,
NEW.user_id
);
);
Note the sequence stored in next_id. This doesn't work as it
complains about next_id in the INSERT statements. Any way to do
something like this? I suppose I could create a function and then
have the rule call the function but this seems like overkill.
Since I have not tried something like this before, I may be off base
but have you tried:
CREATE OR REPLACE RULE user_activity_single_insert AS
ON INSERT TO user_activity_single
DO INSTEAD (
INSERT INTO user_activity(
description,
...
)
VALUES (
NEW.description,
...
);
INSERT INTO user_activity_users (
user_activity_id,
user_id
)
VALUES (
SELECT currval('user_activity_user_activity_id_seq'),
NEW.user_id
);
);
I am assuming user_activity.user_activity_id is a BIGSERIAL or SERIAL.
By coincidence, I had to work this out just the other day for a project
that I'm working on. Aaron's solution looks correct, although you can
just call currval() without the SELECT in this context.
What Collin may not be aware of is that currval(seqX) returns the last
value generated by nextval(seqX) in the same session, so there is no
race between your session and other sessions that may be using the same
sequence. So it's safe to let the insert trigger the next value
automatically, and then call currval(). In contrast, don't use lastval()
for this.
Ross Johnson
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] About Div
Otniel Michael wrote: Mr. Aaron. I am sorry, your solution didn't match in my case. Example for your solution : A = 1 B = 1 C = 1 D = 1 E = 1 F = 1 G = 4 G have 4 candy. Its too much for G. In my case, the solution is : A = 1 B = 1 C = 1 D = 1 E = 2 F = 2 G = 2 The extra candy is given to three child. Do you have the other solution? I need function in postgresql for my case. Because my loop is too slow. Would this achieve the result you're looking for, or must it be done with a function? Assuming you already know the value of totalchildren and totalcandy: BEGIN -- Everyone gets at least this number, which could be zero or more. UPDATE X SET value = (totalcandy / totalchildren); -- Hand the remainder out one at a time until all are gone. UPDATE X SET value = (value + 1) WHERE code = (SELECT code FROM X ORDER BY code DESC LIMIT (totalcandy % totalchildren)); COMMIT Ross ---(end of broadcast)--- TIP 6: explain analyze is your friend
