[SQL] need nelp with aggregate functions
The DB structure is in attachment. I with the number of clients and the number of computers that have processors with "manufacturer" = "INTEL" and "speed" = "2GB" I am trying: select count(c) as qtd_client, count(cm) as qtd_computers from cliente c inner JOIN computer cm on (c.cliente_id = cm.cliente_id) inner join processor p on (cm.processor_id = p.processor_id) inner join speed s on (s.speed_id = p.speed_id) INNER JOIN manufacturer m on (p.manufacturer_id=m.manufacturer_id) where m.manufacturer = 'INTEL' and s.speed = '2GB' but is not working anyone can help me? <> -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] need nelp with aggregate functions
ok, I did: SELECT count(DISTINCT c.cliente_id) as qtd_client,count(cm.cm_id) as qtd_computers GREAT. It works. Please, explain me why and how it works, I wanna learn and do by myself next time :) 2009/11/18 Oliveiros C, > Try substituting the SELECT count(c) as qtd_client,count(cm) as > qtd_computers > > by > > SELECT count( DISTINCT c.cliente_id) as qtd_client,count(/* put here the > primary key of the computer table */ ) as qtd_computers > > Then tell me if it output what you want > > Best, > Oliveiros > > > ----- Original Message - > *From:* Another Trad > *To:* [email protected] > *Sent:* Wednesday, November 18, 2009 4:55 PM > *Subject:* [SQL] need nelp with aggregate functions > > The DB structure is in attachment. > I with the number of clients and the number of computers that have > processors with "manufacturer" = "INTEL" and "speed" = "2GB" > I am trying: > > select count(c) as qtd_client, count(cm) as qtd_computers > > from cliente c > inner JOIN computer cm on (c.cliente_id = cm.cliente_id) > inner join processor p on (cm.processor_id = p.processor_id) > inner join speed s on (s.speed_id = p.speed_id) > INNER JOIN manufacturer m on (p.manufacturer_id=m.manufacturer_id) > > where m.manufacturer = 'INTEL' > and s.speed = '2GB' > > but is not working > anyone can help me? > > -- > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > >
[SQL] LIMIT BASED ON PERCENT
My question is quite simple: I want to select all the records from my table, but I want apply a LIMIT of 20% in the lines. like: select * from client limit 20% I have tried (of course, with no success) this: select * from client limit ((select count(*) from client)*20/100)
Re: [SQL] LIMIT BASED ON PERCENT
No, It doesn't. In my machine: First select ERROR: syntax error at end of input LINE 1: select * from rapadura.cliente limit 20% ^ Second one: ERROR: argument of LIMIT must not contain subqueries Postgres 8.3 2009/11/18 Lee Hachadoorian > Your SQL works for me exactly as it is (substituting a table in my > database). What error are you getting? > > On Wed, Nov 18, 2009 at 2:12 PM, Another Trad > wrote: > > My question is quite simple: I want to select all the records from my > table, > > but I want apply a LIMIT of 20% in the lines. like: > > select * from client limit 20% > > I have tried (of course, with no success) this: > > select * from client limit ((select count(*) from client)*20/100) > > > > > > -- > Lee Hachadoorian > PhD Student, Geography > Program in Earth & Environmental Sciences > CUNY Graduate Center >
Re: [SQL] LIMIT BASED ON PERCENT
But there is any way to do it? 2009/11/18 Guillaume Lelarge > Le mercredi 18 novembre 2009 à 20:24:09, Another Trad a écrit : > > No, It doesn't. > > In my machine: > > > > First select > > ERROR: syntax error at end of input > > LINE 1: select * from rapadura.cliente limit 20% > > ^ > > Second one: > > ERROR: argument of LIMIT must not contain subqueries > > > > Postgres 8.3 > > > > It works for Lee because obviously he's working on a 8.4 server. You can > use > subquery in a LIMIT clause if you have an 8.4 PostgreSQL server. For > earlier > releases, there's no way to do this in a single query. > > > -- > Guillaume. > http://www.postgresqlfr.org > http://dalibo.com >
Re: [SQL] need nelp with aggregate functions
Thank you all very much. This "official" forum is really great. 2009/11/19 Oliveiros C, > Howdy, > > It is quite simple, the joins you used would output a long list of > pairs (client,computer), and, as many people has more than one computer, > many clients will appear repeated, but the COUNT keyword will count them > more than once though. > > The DISTINCT keyword prevents one client from appearing more than once... > > It's basically that... > > Hope this helped > > Best, > Oliveiros > > > - Original Message - > *From:* Another Trad > *To:* Oliveiros C, > *Cc:* [email protected] > *Sent:* Wednesday, November 18, 2009 5:37 PM > *Subject:* Re: [SQL] need nelp with aggregate functions > > ok, I did: SELECT count(DISTINCT c.cliente_id) as > qtd_client,count(cm.cm_id) as qtd_computers > GREAT. It works. > Please, explain me why and how it works, I wanna learn and do by myself > next time :) > > > 2009/11/18 Oliveiros C, > >> Try substituting the SELECT count(c) as qtd_client,count(cm) as >> qtd_computers >> >> by >> >> SELECT count( DISTINCT c.cliente_id) as qtd_client,count(/* put here the >> primary key of the computer table */ ) as qtd_computers >> >> Then tell me if it output what you want >> >> Best, >> Oliveiros >> >> >> - Original Message - >> *From:* Another Trad >> *To:* [email protected] >> *Sent:* Wednesday, November 18, 2009 4:55 PM >> *Subject:* [SQL] need nelp with aggregate functions >> >> The DB structure is in attachment. >> I with the number of clients and the number of computers that have >> processors with "manufacturer" = "INTEL" and "speed" = "2GB" >> I am trying: >> >> select count(c) as qtd_client, count(cm) as qtd_computers >> >> >> from cliente c >> inner JOIN computer cm on (c.cliente_id = cm.cliente_id) >> inner join processor p on (cm.processor_id = p.processor_id) >> inner join speed s on (s.speed_id = p.speed_id) >> INNER JOIN manufacturer m on (p.manufacturer_id=m.manufacturer_id) >> >> >> where m.manufacturer = 'INTEL' >> and s.speed = '2GB' >> >> but is not working >> anyone can help me? >> >> -- >> >> >> -- >> Sent via pgsql-sql mailing list ([email protected]) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> >> >
[SQL] Getting more than one row in UNIQUE fields
My table 'client' has the unique 'client_id'. My test server, when I try: select * from client where client_id = 12 My server returns 3 rows When I try to update this client, his name for example,using my framework (SQLAlchemy), obviously returns the error: "Updated rowcount 3 does not match number of objects updated 1" But the field client_id is unique and has a sequence to auto increment. Anyone ever pass to this?
