[BUGS] Group by in DIVISION
hai,
I am new to SQL...
I am trying to get the result of some mathematical calculation with
group by some key.
I can get the result without grouping. here with i specified my table
information and the SQL i have used to get the result..
can anybody help me to write the correct SQL..
In this i used 4 tables
/* tdos table */
CREATE TABLE tdos
(
dosid integer NOT NULL DEFAULT nextval('dos_sequence'::regclass),
dcode character varying(20) NOT NULL,
count integer NOT NULL,
dosresult integer NOT NULL,
standardcode character varying(12) NOT NULL,
dosnakbn integer NOT NULL,
dosintrsv1 integer,
dosintrsv2 integer,
dosdatersv1 date,
dosdatersv2 date,
dostxtrsv1 text,
dostxtrsv2 text,
CONSTRAINT tdos_pkey PRIMARY KEY (dosid),
CONSTRAINT tdos_mstandard_fkey FOREIGN KEY (standardcode)
REFERENCES mstandard (standardcode) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT tdos_tdlr_fkey FOREIGN KEY (dlrcode)
REFERENCES tdlr (dlrcode) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE tdos OWNER TO postgres;
/*mrea table*/
CREATE TABLE mrea
(
arcode character varying(20) NOT NULL,
aeregistdate date NOT NULL,
arupdatedate date NOT NULL,
arstartdate date,
arenddate date,
areaintrsv1 integer,
areaintrsv2 integer,
areadatersv1 date,
areadatersv2 date,
areatxtrsv1 text,
areatxtrsv2 text,
CONSTRAINT mrea_pkey PRIMARY KEY (arcode)
)
WITH (OIDS=FALSE);
ALTER TABLE mrea OWNER TO postgres;
/*mscn table*/
CREATE TABLE mscn
(
scncode character varying(20) NOT NULL,
arcode character varying(20) NOT NULL,
oldscncode character varying(20) NOT NULL,
dtkbn integer NOT NULL,
inputfilepath text NOT NULL,
outputfilepath text NOT NULL,
backupfilepath text NOT NULL,
nscstartdate date,
nscenddate date,
nscregistdate date NOT NULL,
nscupdatedate date NOT NULL,
nscintrsv1 integer,
nscintrsv2 integer,
nscdatersv1 date,
nscdatersv2 date,
nsctxtrsv1 text,
nsctxtrsv2 text,
CONSTRAINT mscn_pkey PRIMARY KEY (scncode),
CONSTRAINT mkey_fkey FOREIGN KEY (arcode)
REFERENCES mrea (arcode) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (OIDS=FALSE);
ALTER TABLE mscn OWNER TO postgres;
/*mstandard table*/
CREATE TABLE mstandard
(
standardcode character varying(12) NOT NULL,
oldstandardtdcode character varying(12) NOT NULL,
"level" character varying(3) NOT NULL,
title text NOT NULL,
ststartdate date,
stenddate date,
registdate date NOT NULL,
updatedate date NOT NULL,
stdintrsv1 integer,
stdintrsv2 integer,
stddatersv1 date,
stddatersv2 date,
stdtxtrsv1 text,
stdtxtrsv2 text,
CONSTRAINT mstandard_pkey PRIMARY KEY (standardcode)
)
WITH (OIDS=FALSE);
ALTER TABLE mstandard OWNER TO postgres;
SQL i have used:
select ((select count(*) from tdos,tdlr,mscn,mstandard
where tdos.standardcode = mstandard.standardcode and
tdos.dlrcode=tdlr.dlrcode and
tdlr.scncode = mscn.scncode and
tdos.dosresult = 1 and
mscn.scncode = '' and
tdos.dosnakbn = 0 and
tdos.dosdatersv1 between '2006/11/1' and '2007/4/1')) as Periodone,
(select count(*) from tdos,tdlr,mscn,mstandard
where tdos.standardcode = mstandard.standardcode and
tdos.dlrcode=tdlr.dlrcode and
tdlr.scncode = mscn.scncode and
mscn.scncode = '' and
tdos.dosresult = 1 and
tdos.dosnakbn = 0 and
tdos.dosdatersv1 between '2007/4/1' and '2008/11/1' ) as Periodtwo,
Round(((select count(*) from tdos,tdlr,mscn,mstandard
where tdos.standardcode = mstandard.standardcode and
tdos.dlrcode=tdlr.dlrcode and
tdlr.scncode = mscn.scncode and
mscn.scncode = '' and
tdos.dosresult = 1 and
tdos.dosnakbn = 0 and
tdos.dosdatersv1 between '2007/4/1' and '2008/11/1') -
(select count(*) from tdos,tdlr,mscn,mstandard
where tdos.standardcode = mstandard.standardcode and
tdos.dlrcode=tdlr.dlrcode and
tdlr.scncode = mscn.scncode and
tdos.dosresult = 1 and
tdos.dosnakbn = 0 and
mscn.scncode = '' and
tdos.dosdatersv1 between '2006/11/1' and '2007/4/1'))/(1.0*((select count(*)
from tdos,tdlr,mscn,mstandard
where tdos.standardcode = mstandard.standardcode and
tdos.dlrcode=tdlr.dlrcode and
tdlr.scncode = mscn.scncode and
tdos.dosresult = 1 and
tdos.dosnakbn = 0 and
mscn.scncode = '' and
tdos.dosdatersv1 between '2007/4/1' and '2008/11/1')))*100,0)
As Growthrate.
in this SQL i calculated the result only for one scncode.
i need this result for all scncode under mscn table.
Thanks in advance.
John.
--
View this message in context:
http://www.nabble.com/Group-by-in-DIVISION-tp20769635p20769635.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Group by in DIVISION
JOHN_MCY wrote: hai, I am new to SQL... I am trying to get the result of some mathematical calculation with group by some key. I can get the result without grouping. here with i specified my table information and the SQL i have used to get the result.. can anybody help me to write the correct SQL.. ... perhaps someone on the general postgres list could help. the postgres BUGS list is for reporting suspected bugs in the postgres database server. subscription info is here -> http://archives.postgresql.org/pgsql-general/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4554: download crashes
guy barron wrote: > i went on trial with poker tracker did not like> so uninstalled italong with > postgres>then tried holdem manager>went to reinstall postgres. as soon as i > press download it cuts me off from AOL AND PROMPTS me to restart, it also is > not letting me do any downloads at all have i damaged windows during > uninstall please help yours guy. Hello, Your problem is entirely unrelated to PostgreSQL. I suggest you ask the Holdem manager people, not us. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Fall back to alternative tsearch dictionary directory
Hello all,
as recently mentioned on pg-general@, I am currently working on making
installed myspell/unspell dictionary packages (which install
themselves in /usr/share/myspell/dicts, mostly LATIN encoded)
available to PostgreSQL's tsearch/word stemming in Debian/Ubuntu.
So far I wrote the postgresql-common infrastructure to mangle these
dictionary/affix files to become palatable for PostgreSQL (recoding to
UTF-8, renaming to lowercase, changing file suffix) and install them
into /var/cache/postgresql/dicts/ whenever a {hun,my}spell-* package
is installed or updated.
The remaining bit is teaching postgresql to actually look into
/var/cache/postgresql/dicts/ if it does not find a matching
dictionary/affix file in ${sharepath}/tsearch_data/.
The reasons why I'm not using ${sharepath}/tsearch_data/ in the first
place are that
- it's autogenerated data, as opposed to files statically shipped in
a package
- I do not want to conflict to/overwrite files which the admin
manually put there.
I created an initial demo patch which provides this fallback. It works
great, it passes my test cases (which set up tsearch full text search
and stemming handling) and is pretty simple, too.
However, the path is hardcoded so far, which is of course bad for
upstream inclusion. So this should either become a ./configure option
--with-tsearch-dict-fallback=path (or similar), or even a new optional
configuration parameter for postgresql.conf.
However, before I work on that, I'd like to collect some opinions
about the general idea, and whether you prefer autoconf option or
postgresql.conf, or whether you wouldn't accept it at all?
Thanks a lot in advance!
Martin
--
Martin Pitt| http://www.piware.de
Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org)
# Description: If a tsearch/stem dictionary is not found in /usr/share/postgresql/VERSION/tsearch_data/, fall back to /var/cache/postgresql/dicts/, where postgresql-common creates them from system directories.
# Ubuntu: https://launchpad.net/bugs/301770
--- ./src/backend/tsearch/ts_utils.c.orig 2008-12-01 06:47:28.0 -0800
+++ ./src/backend/tsearch/ts_utils.c 2008-12-01 07:05:14.0 -0800
@@ -15,6 +15,7 @@
#include "postgres.h"
#include
+#include
#include "miscadmin.h"
#include "tsearch/ts_locale.h"
@@ -36,7 +37,7 @@
const char *extension)
{
char sharepath[MAXPGPATH];
- char *result;
+ char *result, *system_result;
/*
* We limit the basename to contain a-z, 0-9, and underscores. This may
@@ -58,6 +59,21 @@
snprintf(result, MAXPGPATH, "%s/tsearch_data/%s.%s",
sharepath, basename, extension);
+ /* fall back to /var/cache/postgresql/dicts/ */
+ if (access(result, R_OK) != 0)
+ {
+ system_result = palloc(MAXPGPATH);
+ snprintf(system_result, MAXPGPATH, "/var/cache/postgresql/dicts/%s.%s",
+ basename, extension);
+ if (access(system_result, R_OK) == 0)
+ {
+ pfree(result);
+ result = system_result;
+ }
+ else
+ pfree(system_result);
+ }
+
return result;
}
signature.asc
Description: Digital signature
Re: [BUGS] Fall back to alternative tsearch dictionary directory
Martin Pitt <[EMAIL PROTECTED]> writes:
> So far I wrote the postgresql-common infrastructure to mangle these
> dictionary/affix files to become palatable for PostgreSQL (recoding to
> UTF-8, renaming to lowercase, changing file suffix) and install them
> into /var/cache/postgresql/dicts/ whenever a {hun,my}spell-* package
> is installed or updated.
> The remaining bit is teaching postgresql to actually look into
> /var/cache/postgresql/dicts/ if it does not find a matching
> dictionary/affix file in ${sharepath}/tsearch_data/.
I can't see any reason whatever to not put them into
${sharepath}/tsearch_data/. It's not like you're expecting to be
able to share them with other applications.
> The reasons why I'm not using ${sharepath}/tsearch_data/ in the first
> place are that
> - it's autogenerated data, as opposed to files statically shipped in
>a package
> - I do not want to conflict to/overwrite files which the admin
>manually put there.
Seems like it'd be quite sufficient to choose a specialized naming
policy within tsearch_data, say es_ES.aff -> system_es_es.aff. I don't
think moving stuff into a different subdirectory makes conflicts a
non-problem; it just means that half the world will be unhappy with the
search order you chose.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Fall back to alternative tsearch dictionary directory
Hi Tom,
Tom Lane [2008-12-01 19:51 -0500]:
> I can't see any reason whatever to not put them into
> ${sharepath}/tsearch_data/. It's not like you're expecting to be
> able to share them with other applications.
No, not for sharing. I just don't like them to be in /usr, but that's
by and large a stylistic preference, and I won't dwell on it.
> Seems like it'd be quite sufficient to choose a specialized naming
> policy within tsearch_data, say es_ES.aff -> system_es_es.aff.
Works for me, too.
> I don't think moving stuff into a different subdirectory makes
> conflicts a non-problem; it just means that half the world will be
> unhappy with the search order you chose.
IMHO there is really just one sensible ordering here. Always prefer
the ones installed by hand, and only if they are not present, fall
back to the system defaults. The other way around would mean that the
admin couldn't do local overriding any more.
Thanks,
Martin
--
Martin Pitt| http://www.piware.de
Ubuntu Developer (www.ubuntu.com) | Debian Developer (www.debian.org)
signature.asc
Description: Digital signature
