Re: [SQL] SUM() & GROUP BY
Hi again I have two tables I would like to query i.e. service table and
division table
SELECT s.pd_geo, COUNT(s.pd_geo) FROM ser s GROUP BY s.pd_geo;
OUTPUT:
pd_geo | count
--
1| 49
2| 39
3|6
4|54
SELECT d.divisions_name, d.divisions_id)
FROM ser s, ser_divisions d
WHERE d.divisions_id = s.ser_divisions;
division_name | divisions_id
--
DEC| 6
DEPI | 7
DRC|8
How can I create a query that displays How the divisions answered the
question please do assist.
Regrards
Martin W. Kuria
>From: Oliver Elphick <[EMAIL PROTECTED]>
>To: "Muhyiddin A.M Hayat" <[EMAIL PROTECTED]>
>CC: [EMAIL PROTECTED]
>Subject: Re: [SQL] SUM() & GROUP BY
>Date: Sun, 28 Sep 2003 20:56:56 +0100
>
>On Sun, 2003-09-28 at 19:01, Muhyiddin A.M Hayat wrote:
> >
> > hotel=# SELECT
> > hotel-# "public".billing.id,
> > hotel-# "public".billing.guest_id,
> > hotel-# "public".billing.trx_date,
> > hotel-# "public".billing.trx_time,
> > hotel-# "public".billing.payment_method,
> > hotel-# "public".billing.tax,
> > hotel-# "public".billing.dep_id,
> > hotel-# "public".department."name",
> > hotel-# SUM("public".items.price) AS total,
> > hotel-# "public".billing.amount_paid
> > hotel-# FROM
> > hotel-# "public".billing_items
> > hotel-# INNER JOIN "public".billing ON
> > ("public".billing_items.billing_id =
"public".billing.id)
> > hotel-# INNER JOIN "public".department ON
("public".billing.dep_id =
> > "public".department.id)
> > hotel-# INNER JOIN "public".items ON
> > ("public".billing_items.items_id =
"public".items.id)
> > hotel-# GROUP BY "public".billing.id;
> > ERROR: Attribute billing.guest_id must be GROUPed or used in an
> > aggregate function
> > hotel=#
> >
> > What Worng ??
>
>Any items in the select list need to be aggregated (e.g.
>SUM("public".items.price)) or mentioned in the GROUP BY list.
Suppose
>there are several billing.guest_id values for each billing.id; which
>value should be listed in the output?
>
>
>--
>Oliver Elphick[EMAIL PROTECTED]
>Isle of Wight, UK
http://www.lfix.co.uk/oliver
>GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
>
> "Blessed is the man that endureth temptation; for when
> he is tried, he shall receive the crown of life, which
> the Lord hath promised to them that love him."
> James 1:12
>
>
>---(end of broadcast)---
>TIP 8: explain analyze is your friend
_
Help STOP SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] not really SQL but I need info on BLOBs
Thanks for the reply. Are there (in your opinion) reasons why you would choose to store the images in the db? My feelings are that it comes down to access. If I store the images in the db, my only access to these images is to retrieve the image do some work on it and reupload it. If I store the image as a file and link to it, I can edit the image to my hearts content by mounting the volume and opening it in an image editor and saving. there is nothing more to do. archiving and backups are easier also. my tape system talks to my server and backs up the images. I believe it would be easier to recover from a problem. in your solution, how do you retrieve your image? do you need to mount the volume where the images live? I guess I am asking "If your user/client queries the db for an image, they may want to view it? How do you send them the data?" I am working on a project where the IT department is pushing really hard to have all the images in the db. I don't know what the agenda is. I am hopeful to come up with reasons either why this is good or not good. Thanks, Ted --- Andrei Bintintan <[EMAIL PROTECTED]> wrote: > I suggest to store the images on the disc and write > in the db only the path. > I am using the same solution. The only problem is > that I have to make the > backups separately(in my case). Also this helps more > in accessing the > images, you don't have to access for each image the > database, you just have > to get the file path. > > Think also at the dump size of the database (with > blobs). (if you have 100GB > of photos..the dump will be also that big) > > I never tied to put the database on 2 disks, I read > somewhere that is > possible but I never tried it. In my situation I > don't need this, because > the size of the DB is not quite that big. But for > the photos we will use > more discs (we are not using 2 yet) and there is no > difference in this case > how many discs you use, because in the DB you store > only the path. > > I see it this way more simple, maybe other have > other opinions. > > Best regards, > Andy. > > - Original Message - > From: "Theodore Petrosky" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Wednesday, May 05, 2004 6:10 PM > Subject: [SQL] not really SQL but I need info on > BLOBs > > > > Hi, > > > > I am starting a new project where I need to store > a > > lot of tiff images. So the question beckons... > which > > is better (not correct)? > > store a blob in the database and let the db manage > the > > data or > > store the image in a directory and store the path > to > > the data in the db. > > > > What are your opinions? Is one method better than > the > > other or are there simply different trade offs? > > > > If I store the tiff on the computer and only store > the > > path in the database it would be easier to add > more > > disk space to the computer without any changes to > the > > db. Also, there would be 'virtually' unlimited > storage > > space (only limited by the OS) > > > > However, it looks like it would be easier to > create a > > connection to the database to access the blobs. > > > > I would love to hear not just what others have > done > > but why they did it? > > > > Thanks for listening... > > > > BTW, I really like the zeroconf (rendezvous) > support > > in postgresql. > > > > Ted > > > > > > > > > > __ > > Do you Yahoo!? > > Win a $20,000 Career Makeover at Yahoo! HotJobs > > > http://hotjobs.sweepstakes.yahoo.com/careermakeover > > > > ---(end of > broadcast)--- > > TIP 8: explain analyze is your friend > > > > > > ---(end of > broadcast)--- > TIP 3: 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 __ Do you Yahoo!? Win a $20,000 Career Makeover at Yahoo! HotJobs http://hotjobs.sweepstakes.yahoo.com/careermakeover ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] new and old as parameter in a function
Hi. I'm having some trouble, and I would like to receive some input by the pg community... I would like to write a failry complex PLPGSQL trigger that calls a secondary function. The trigger calls the function with the NEW record that returns a variable of type RECORD whith the NEW variable modified accordingly to a series of actions. By Reading the documentation, I understand that I can write a PLPGSQL function that receives a parameter of type RECORD, and returns a parameter of type RECORD, and that this function is callable from a Trigger. But: I haven't been able to write what's expressed above... is there somebody that can provide some help and/or some examples? thanks in advance, = Riccardo G. Facchini ---(end of broadcast)--- TIP 3: 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] not really SQL but I need info on BLOBs
On Thu, May 06, 2004 at 04:46:22AM -0700, Theodore Petrosky wrote: > Thanks for the reply. Are there (in your opinion) > reasons why you would choose to store the images in > the db? Transactional integrity. If there's a risk that people are going to be deleting, &c. these images, then you can end up with references in the database to files that don't exist, because the filesystem operations can't be made subject to the transactions of the database. A -- Andrew Sullivan | [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] new and old as parameter in a function
"Riccardo G. Facchini" <[EMAIL PROTECTED]> writes: > By Reading the documentation, I understand that I can write a PLPGSQL > function that receives a parameter of type RECORD, You misread it --- that's not presently supported. regression=# create function foo(record) returns int as ' regression'# begin regression'# return $1.f1; regression'# end' language plpgsql; ERROR: plpgsql functions cannot take type record However you can make a plpgsql function that accepts a parameter of a specific (named) rowtype: regression=# create table fooey(f1 int, f2 text); CREATE TABLE regression=# create function foo(fooey) returns int as ' regression'# begin regression'# return $1.f1; regression'# end' language plpgsql; CREATE FUNCTION regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] not really SQL but I need info on BLOBs
Now of corse it depends on the application that you are making, where you put the files. In my situation it is not so complicated, because we're not working on the images, so from this point of view they are "readonly". Out clients that are asking for photos have webaccess to a folder where we put the the photos and we just give the right link, or they receive the photos in pdf documents. I have the images on the same server as the database, so this is not a problem to access them. But also if you have to make a mount on other system I see no problem to this. Only if you will have to write the image file back, then you will have to give write access to that file. In our situation the image files are "web accessed" so there is NO way that we give write access to that folder(I mean for the clients). Now honestly the size of the DB makes me to worry at the point when you put the images in the database. You cannot make simple dumps. I have at the moment 12 GB of photos and the backup script for these takes quite some time. Now there is another risk when you put the photos on the disk... there is the possibility that you have the link in the DB but you don't have the photo, or the file is corrupted. This can happen. It is up to you to decide. I don't know if there are also some performance losses or not, but from what I can imagine to read an image from DB takes more time as to read it from a file. I know a lot of people that are storing the images on disk and not in the database. If you store the images in the DB the handling becomes more complicated. You will have always have to read the image and create a temporary file in order to work with it. In file cases you just copy it. I think it is more flexible the storage on the disk. But... search the forums, or hope that somedoby else answers also. Best regards, Andy. - Original Message - From: "Theodore Petrosky" <[EMAIL PROTECTED]> To: "Andrei Bintintan" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, May 06, 2004 2:46 PM Subject: Re: [SQL] not really SQL but I need info on BLOBs > Thanks for the reply. Are there (in your opinion) > reasons why you would choose to store the images in > the db? My feelings are that it comes down to access. > > If I store the images in the db, my only access to > these images is to retrieve the image do some work on > it and reupload it. If I store the image as a file and > link to it, I can edit the image to my hearts content > by mounting the volume and opening it in an image > editor and saving. there is nothing more to do. > > archiving and backups are easier also. my tape system > talks to my server and backs up the images. I believe > it would be easier to recover from a problem. > > in your solution, how do you retrieve your image? do > you need to mount the volume where the images live? I > guess I am asking "If your user/client queries the db > for an image, they may want to view it? How do you > send them the data?" > > I am working on a project where the IT department is > pushing really hard to have all the images in the db. > I don't know what the agenda is. I am hopeful to come > up with reasons either why this is good or not good. > > Thanks, > > Ted > > --- Andrei Bintintan <[EMAIL PROTECTED]> wrote: > > I suggest to store the images on the disc and write > > in the db only the path. > > I am using the same solution. The only problem is > > that I have to make the > > backups separately(in my case). Also this helps more > > in accessing the > > images, you don't have to access for each image the > > database, you just have > > to get the file path. > > > > Think also at the dump size of the database (with > > blobs). (if you have 100GB > > of photos..the dump will be also that big) > > > > I never tied to put the database on 2 disks, I read > > somewhere that is > > possible but I never tried it. In my situation I > > don't need this, because > > the size of the DB is not quite that big. But for > > the photos we will use > > more discs (we are not using 2 yet) and there is no > > difference in this case > > how many discs you use, because in the DB you store > > only the path. > > > > I see it this way more simple, maybe other have > > other opinions. > > > > Best regards, > > Andy. > > > > - Original Message - > > From: "Theodore Petrosky" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Wednesday, May 05, 2004 6:10 PM > > Subject: [SQL] not really SQL but I need info on > > BLOBs > > > > > > > Hi, > > > > > > I am starting a new project where I need to store > > a > > > lot of tiff images. So the question beckons... > > which > > > is better (not correct)? > > > store a blob in the database and let the db manage > > the > > > data or > > > store the image in a directory and store the path > > to > > > the data in the db. > > > > > > What are your opinions? Is one method better than > > the > > > other or are there simply different trade offs? > > > > > > If I store
[SQL] Subselect returning 2 columns
Hello, Is correct to do something like?: SELECT row1,row2 FROM mytable WHERE (row1,row3) IN (SELECT row1 as p1,MAX(row3) as p2 FROM mytable WHERE row3 <= current_date GROUP BY row1) I mean, I don't see documentation about subselects returning more than one column, but it seems to work... Thanks, Francesc. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Subselect returning 2 columns
Francesc Leveque <[EMAIL PROTECTED]> writes: > Is correct to do something like?: > SELECT row1,row2 FROM mytable > WHERE (row1,row3) IN > (SELECT row1 as p1,MAX(row3) as p2 FROM mytable > WHERE row3 <= current_date > GROUP BY row1) Sure. > I mean, I don't see documentation about subselects returning more than > one column, but it seems to work... http://www.postgresql.org/docs/7.4/static/functions-subquery.html#AEN12497 regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
