pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDiffcult query
5+ messages / 4 participants
[nested] [flat]
* Diffcult query
@ 2003-03-21 17:41 Dan Winslow <d.winslow@cox.net>
0 siblings, 3 replies; 5+ messages in thread
From: Dan Winslow @ 2003-03-21 17:41 UTC (permalink / raw)
To: pgsql-sql
Hi folks. This is giving me fits. I feel like it oughta be simple, but
apparantly its not...I think.
Given a table :
create table tablea (
code1 varchar(32),
code2 varchar(32),
cost int
);
and the rows
code1 code2 cost
----------------------------------
"aaa" "bbb" 2
"ddd" "eee" 3
"bbb" "aaa" 6
"ggg" "hhh" 4
I need a ( preferably single ) query that will sum the costs for all unique
pairs of codes, without regard to column order. That is, for summing
purposes, rows 1 and 3 are identical, and should produce the sum of 8 for
the unordered pair("aaa","bbb"). It should also, of course, prevent the case
where ("bbb","aaa") is considered a seperate pair. Any ideas would be much
appreciated.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Diffcult query
@ 2003-03-21 19:45 Josh Berkus <josh@agliodbs.com>
parent: Dan Winslow <d.winslow@cox.net>
2 siblings, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2003-03-21 19:45 UTC (permalink / raw)
To: Dan Winslow <d.winslow@cox.net>; pgsql-sql
Dan,
> Hi folks. This is giving me fits. I feel like it oughta be simple, but
> apparantly its not...I think.
> I need a ( preferably single ) query that will sum the costs for all unique
> pairs of codes, without regard to column order. That is, for summing
> purposes, rows 1 and 3 are identical, and should produce the sum of 8 for
> the unordered pair("aaa","bbb"). It should also, of course, prevent the case
> where ("bbb","aaa") is considered a seperate pair. Any ideas would be much
> appreciated.
It's not simple, because your DB design is poorly normalized. My first
suggestion would be to make some design changes to your schema. Is that
possible?
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Diffcult query
@ 2003-03-21 23:17 Josh Berkus <josh@agliodbs.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2003-03-21 23:17 UTC (permalink / raw)
To: Dan Winslow <d.winslow@cox.net>; +Cc: pgsql-sql
Dan,
> No, it is not my design, unfortunately.
Then there is no simple way.
> > I need a ( preferably single ) query that will sum the costs for all
> unique
> > pairs of codes, without regard to column order. That is, for summing
> > purposes, rows 1 and 3 are identical, and should produce the sum of 8 for
> > the unordered pair("aaa","bbb"). It should also, of course, prevent the
> case
> > where ("bbb","aaa") is considered a seperate pair. Any ideas would be much
> > appreciated.
1) Create a function which will take two text variables and concatenate them
in alpha sort order. The idea of this function:
fn_distinct_pair('aaa','bbb') = 'aaa bbb'
fn_distinct_pair('bbb','aaa') = 'aaa bbb'
Thus:
CREATE FUNCTION fn_distinct_pair (text, text) returns text as'
SELECT (CASE WHEN $1 < $2 THEN
$1 || '' '' || $2
ELSE
$2 || '' '' || $1
END);
' LANGUAGE SQL;
(of course, this gets much more complicated if you have more than two columns
or if any of the columns can be NULL)
2) Group by the results of that function.
Good luck!
-Josh Berkus
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Diffcult query
@ 2003-03-22 03:59 Lee Harr <missive@frontiernet.net>
parent: Dan Winslow <d.winslow@cox.net>
2 siblings, 0 replies; 5+ messages in thread
From: Lee Harr @ 2003-03-22 03:59 UTC (permalink / raw)
To: pgsql-sql
In article <d9Iea.117330$JE5.48428@news2.central.cox.net>, Dan Winslow wrote:
> Hi folks. This is giving me fits. I feel like it oughta be simple, but
> apparantly its not...I think.
>
> Given a table :
>
> create table tablea (
> code1 varchar(32),
> code2 varchar(32),
> cost int
> );
>
> and the rows
>
> code1 code2 cost
> ----------------------------------
> "aaa" "bbb" 2
> "ddd" "eee" 3
> "bbb" "aaa" 6
> "ggg" "hhh" 4
>
> I need a ( preferably single ) query that will sum the costs for all unique
> pairs of codes, without regard to column order. That is, for summing
> purposes, rows 1 and 3 are identical, and should produce the sum of 8 for
> the unordered pair("aaa","bbb"). It should also, of course, prevent the case
> where ("bbb","aaa") is considered a seperate pair. Any ideas would be much
> appreciated.
>
>
Maybe you could create a function that will return all of the rows
with a particular pair of codes.
Then I think you could GROUP BY that function...
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Diffcult query
@ 2003-03-22 09:52 Harald Fuchs <nospam@sap.com>
parent: Dan Winslow <d.winslow@cox.net>
2 siblings, 0 replies; 5+ messages in thread
From: Harald Fuchs @ 2003-03-22 09:52 UTC (permalink / raw)
To: pgsql-sql
In article <d9Iea.117330$JE5.48428@news2.central.cox.net>,
"Dan Winslow" <d.winslow@cox.net> writes:
> Hi folks. This is giving me fits. I feel like it oughta be simple, but
> apparantly its not...I think.
> Given a table :
> create table tablea (
> code1 varchar(32),
> code2 varchar(32),
> cost int
> );
> and the rows
> code1 code2 cost
> ----------------------------------
> "aaa" "bbb" 2
> "ddd" "eee" 3
> "bbb" "aaa" 6
> "ggg" "hhh" 4
> I need a ( preferably single ) query that will sum the costs for all unique
> pairs of codes, without regard to column order. That is, for summing
> purposes, rows 1 and 3 are identical, and should produce the sum of 8 for
> the unordered pair("aaa","bbb"). It should also, of course, prevent the case
> where ("bbb","aaa") is considered a seperate pair. Any ideas would be much
> appreciated.
How about this one?
SELECT CASE WHEN code1 < code2 THEN code1 ELSE code2 END ||
'\0' ||
CASE WHEN code1 < code2 THEN code2 ELSE code1 END AS codes,
SUM(cost) AS costs
FROM tablea
GROUP BY codes;
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-03-22 09:52 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-21 17:41 Diffcult query Dan Winslow <d.winslow@cox.net>
2003-03-21 19:45 ` Josh Berkus <josh@agliodbs.com>
2003-03-21 23:17 ` Josh Berkus <josh@agliodbs.com>
2003-03-22 03:59 ` Lee Harr <missive@frontiernet.net>
2003-03-22 09:52 ` Harald Fuchs <nospam@sap.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox