[SQL] Anual Query
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello, Can any of you help me with this query?, I need to retrieve the sum(units) and sum(cost) for each client in every month in the same row, something like this: client|UnJan|CostJan|UnFeb|CostFeb|UnMar|CostMar ...|UnDec|CostDec - 100058|580 |47384 |746 |87438 |536 |21653 ... |857|754875 (wrong data, just to show) Now I get this result using several querys (one for month and worse for client too) but this, of course its too expensive. Here is a table sample. orderId | client | date| product | Un | cost - -- 1001299 | 100058 | 20050103 | 759936 | 1 | 2375 1001299 | 100058 | 20050103 | 759928 | 1 | 2375 1001300 | 100078 | 20050103 | 759936 | 1 | 2375 1001301 | 100017 | 20050103 | 759928 | 1 | 2375 1001302 | 19 | 20050103 | 759936 | 2 | 4750 1001302 | 19 | 20050203 | 756304 | 1 | 6669 1001303 | 19 | 20050203 | 759936 | 2 | 4750 1001304 | 15 | 20050203 | 759936 | 20 | 47500 1001306 | 100013 | 20050203 | 759936 | 2 | 4750 1001306 | 100013 | 20050203 | 759928 | 2 | 4750 1001307 | 100013 | 20050203 | 759936 | 4 | 9500 1001308 | 100013 | 20050203 | 759936 | 2 | 4750 1001309 | 100050 | 20050303 | 759936 | 2 | 4750 1001310 | 100050 | 20050303 | 759936 | 4 | 9500 Thanks for any idea. Mauricio Fernández A. Ingeniero de Sistemas U. Autónoma de Manizales -BEGIN PGP SIGNATURE- Version: PGPfreeware 6.0.2i iQA/AwUBQ4sgNc4irmZP1c6WEQLcPgCeLQNXphTXFXqVFZcKxrIO7aAfCEMAn397 jOO4lULVn/3bPLm6du7hGJeR =YSsL -END PGP SIGNATURE- ---(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] Anual Query
At 11:20 AM 11/28/05, Mauricio Fernandez A. wrote: Can any of you help me with this query?, I need to retrieve the sum(units) and sum(cost) for each client in every month in the same row, something like this: client|UnJan|CostJan|UnFeb|CostFeb|UnMar|CostMar ...|UnDec|CostDec - 100058|580 |47384 |746 |87438 |536 |21653 ... |857 |754875 (wrong data, just to show) Now I get this result using several querys (one for month and worse for client too) but this, of course its too expensive. Here is a table sample. orderId | client | date| product | Un | cost - -- 1001299 | 100058 | 20050103 | 759936 | 1 | 2375 1001299 | 100058 | 20050103 | 759928 | 1 | 2375 1001300 | 100078 | 20050103 | 759936 | 1 | 2375 1001301 | 100017 | 20050103 | 759928 | 1 | 2375 1001302 | 19 | 20050103 | 759936 | 2 | 4750 1001302 | 19 | 20050203 | 756304 | 1 | 6669 1001303 | 19 | 20050203 | 759936 | 2 | 4750 1001304 | 15 | 20050203 | 759936 | 20 | 47500 1001306 | 100013 | 20050203 | 759936 | 2 | 4750 1001306 | 100013 | 20050203 | 759928 | 2 | 4750 1001307 | 100013 | 20050203 | 759936 | 4 | 9500 1001308 | 100013 | 20050203 | 759936 | 2 | 4750 1001309 | 100050 | 20050303 | 759936 | 2 | 4750 1001310 | 100050 | 20050303 | 759936 | 4 | 9500 Search google and/or archives of this and/or other pgsql lists for "crosstab" and/or "pivot table". I think there might also be something in contrib/ that could help. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] DEFAULT Constraint based on table type?
Lets say I have the following tables. CREATE TABLE animals(id primary key, name varchar, type varchar); CREATE TABLE dogs (breed varchar)INHERITS (animals); CREATE TABLE birds (bool hasFeathers) INHERITS (animals); Is there a way I can specify a default on the child table that will populate the 'type' column? For example, if I am inserting a row in table DOGS, I would always want the default value for column TYPE to be 'DOG'. If I am inserting into BIRDS type 'BIRD'. I know that I could add individual triggers on each table that set the TYPE field to a default value on insert but I wanted a more simple solution like setting a DEFAULT table-constraint. Also, In java, this could be done on a parent object by overriding a constructor or method, using the Class object or instanceof. Is there anyway for a table to "know" it's "class" in this scenario? Thanks, -Aaron ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] DEFAULT Constraint based on table type?
On 11/28/05, Announce <[EMAIL PROTECTED]> wrote: > Lets say I have the following tables. > > CREATE TABLE animals(id primary key, name varchar, type varchar); > CREATE TABLE dogs (breed varchar)INHERITS (animals); > CREATE TABLE birds (bool hasFeathers) INHERITS (animals); > > Is there a way I can specify a default on the child table that will populate > the 'type' column? For example, if I am inserting a row in table DOGS, I > would always want the default value for column TYPE to be 'DOG'. If I am > inserting into BIRDS type 'BIRD'. > > I know that I could add individual triggers on each table that set the TYPE > field to a default value on insert but I wanted a more simple solution like > setting a DEFAULT table-constraint. > > Also, In java, this could be done on a parent object by overriding a > constructor or method, using the Class object or instanceof. Is there > anyway for a table to "know" it's "class" in this scenario? > > Thanks, > > -Aaron > CREATE TABLE animals(id primary key, name varchar, type varchar); CREATE TABLE dogs (breed varchar)INHERITS (animals); ALTER TABLE dogs ALTER COLUMN type SET DEFAULT 'DOG'; CREATE TABLE birds (bool hasFeathers) INHERITS (animals); ALTER TABLE birds ALTER COLUMN type SET DEFAULT 'BIRD'; -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] DEFAULT Constraint based on table type?
On Mon, 2005-11-28 at 14:22 -0600, Announce wrote: > Lets say I have the following tables. > > CREATE TABLE animals(id primary key, name varchar, type varchar); > CREATE TABLE dogs (breed varchar)INHERITS (animals); > CREATE TABLE birds (bool hasFeathers) INHERITS (animals); r=# alter table birds alter type set default 'Bird'; ALTER TABLE r=# \d birds Table "public.birds" Column| Type| Modifiers -+---+--- id | integer | not null name| character varying | type| character varying | default 'Bird'::character varying hasfeathers | boolean | Inherits: animals r=# \d animals Table "public.animals" Column | Type| Modifiers +---+--- id | integer | not null name | character varying | type | character varying | Indexes: "animals_pkey" PRIMARY KEY, btree (id) -- ---(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] join if all matches
On Tue, Nov 22, 2005 at 10:30:17 +0200, Sim Zacks <[EMAIL PROTECTED]> wrote: > I am trying to figure out an sql statement and I was hoping someone could > help. I'm having brainfreeze right now. > > Table Rules > RuleID > RuleName > > Table RuleAgents > RuleAgentID > RuleID > Agent > > Table RuleActions > RuleActionID > RuleID > Action > > I am passing in an array of agents into a function and I would like to see > all the actions for which all of the agents of a rule have been found. > > For example: > > Rules > RuleIDRuleName > 1 Rule1 > 2 Rule2 > > RuleAgents > RuleAgentIDRuleIDAgent > 1115 > 2117 > 3291 > > RuleActions > RuleActionIDRuleIDAction > 111000 > 211005 > 311010 > 411099 > 521500 > 629807 > 721409 > > If I pass into my function 15 then I don't want it to return anything, > because rule1 requires both 15 and 17. > If I pass in 19 then I want it to return a resultset including the actions > with Rule2 (1500,9807,1409) > If I pass in both 15 and 17 then I want it to return all the actions with > Rule1 > > Any thoughts on the join? One approach is to first eliminate rule agents that have agents in your list. Then any remaining rules in ruleagents are rules that should be removed from the rule list. Then you canoutput ruleactions that have rules in this modified list. You should be able to build a query doing this using a couple of levels of IN / NOT IN. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
