Re: [SQL] join problem
What is the correct query??? thanks Ragnar wrote: On fim, 2007-06-21 at 08:46 -0500, A. R. Van Hook wrote: if I query for the total deposit using select sum(deposit) from invoice where cusid = 2128" I also get 1179.24, also the correct amount If I try an inclusive query using the following: select sum(i.rowtot + i.tax) as tot, sum(v.deposit) as deposit from cai c join invoice v on (v.cusid = c.cusid) left join invoiceitems i on (v.ivid = i.ivid) where v.cusid = 2128 group by c.cusid I get tot| deposit --+- 1179.240 | 2819.24 you are adding the invoice deposit once for each item gnari ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Arthur R. Van Hook Mayor - Retired The City of Lake Lotawana [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] (816) 578-4704 - Home (816) 564-0769 - Cell ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Counting all rows
I need to get statistics from a bunch of tables. Simply the number of records in them. The query plan looks like this: => explain select count(id) from stuff; QUERY PLAN --- Aggregate (cost=1629.69..1629.70 rows=1 width=8) -> Seq Scan on stuff (cost=0.00..1517.75 rows=44775 width=8) The sequential scan kind of worries me. Is there a better/faster way to do this? S. S. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Counting all rows
Stefan Arentz <[EMAIL PROTECTED]> schrieb: > I need to get statistics from a bunch of tables. Simply the number of > records in them. > > The query plan looks like this: > > => explain select count(id) from stuff; > QUERY PLAN > --- > Aggregate (cost=1629.69..1629.70 rows=1 width=8) >-> Seq Scan on stuff (cost=0.00..1517.75 rows=44775 width=8) > > The sequential scan kind of worries me. Is there a better/faster way to do > this? Yes. A "select count(*) from foo;" enforces a seq. scan. Solutions for this are discussed here: http://www.varlena.com/GeneralBits/120.php Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Counting all rows
On 6/23/07, Andreas Kretschmer <[EMAIL PROTECTED]> wrote: Stefan Arentz <[EMAIL PROTECTED]> schrieb: > I need to get statistics from a bunch of tables. Simply the number of > records in them. > > The query plan looks like this: > > => explain select count(id) from stuff; > QUERY PLAN > --- > Aggregate (cost=1629.69..1629.70 rows=1 width=8) >-> Seq Scan on stuff (cost=0.00..1517.75 rows=44775 width=8) > > The sequential scan kind of worries me. Is there a better/faster way to do > this? Yes. A "select count(*) from foo;" enforces a seq. scan. Solutions for this are discussed here: http://www.varlena.com/GeneralBits/120.php Thank you Andreas. That is exactly what I was looking for. S. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] join problem
On lau, 2007-06-23 at 04:15 -0500, A. R. Van Hook wrote: > Ragnar wrote: > > On fim, 2007-06-21 at 08:46 -0500, A. R. Van Hook wrote: > >> If I try an inclusive query using the following: > >> select > >> sum(i.rowtot + i.tax) as tot, > >> sum(v.deposit) as deposit > >> from cai c > >> join invoice v on (v.cusid = c.cusid) > >> left join invoiceitems i on (v.ivid = i.ivid) > >> where v.cusid = 2128 > >> group by > >> c.cusid > >> I get > >> tot| deposit > >> --+- > >> 1179.240 | 2819.24 > > > > you are adding the invoice deposit once for each item > > > What is the correct query??? sum each invoice separately, and then group the sums by cusid. for example: select vcusid as cusid, sum(vtot) as tot, sum(vdeposit) as deposit from ( select v.cusid as vcusid, v.ivid as vivid, sum(i.rowtot + i.tax) as vtot, sum(v.deposit)/count(*) as vdeposit from invoice as v left join invoiceitems as i on (v.ivid = i.ivid) group by v.cusid, v.ivid ) as vsums where vsums.vcusid=2128 group by vsums.vcusid hope this helps gnari ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Counting all rows
On Saturday 23 June 2007 05:38:56 Andreas Kretschmer wrote: > Stefan Arentz <[EMAIL PROTECTED]> schrieb: > > I need to get statistics from a bunch of tables. Simply the number of > > records in them. > > > > The query plan looks like this: > > > > => explain select count(id) from stuff; > > QUERY PLAN > > --- > > Aggregate (cost=1629.69..1629.70 rows=1 width=8) > >-> Seq Scan on stuff (cost=0.00..1517.75 rows=44775 width=8) > > > > The sequential scan kind of worries me. Is there a better/faster way to > > do this? > > Yes. > > A "select count(*) from foo;" enforces a seq. scan. Solutions for this > are discussed here: http://www.varlena.com/GeneralBits/120.php > > > Andreas Is there any neat trick for doing this for paging estimates? It looks like the only way to get a result count from a more complex query is to either fetch a count first, or to fetch all records and count them in the application. -- ~ manchicken <>< (A)bort, (R)etry, (I)nfluence with large hammer. 09 F9 11 02 9D 74 E3 5B D8 41 56 C5 63 56 88 C0 Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html ---(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
