[prev in list] [next in list] [prev in thread] [next in thread]
List: odtug-sqlplus-l
Subject: RE: Please ignore my previous message eon the MERGE statement - I figure dit
From: "John Flack" <JohnF () smdi ! com>
Date: 2008-02-25 20:25:27
Message-ID: F001.0061331E.20080225122527 () fatcity ! com
[Download RAW message or body]
If your source table looks like this:
Key_field, start_date, end_date, price
And there can be multiple rows with the same value in key_field, but you
want them compressed into one row for each distinct value in key_field,
do this:
SELECT key_field, MIN(start_date) start_date, MAX(end_date) end_date,
AVG(price) price
FROM source_table
GROUP BY key_field;
If there are other fields that need to be included, but can't be
averaged. Counted, or summed, a MIN or MAX will often do the trick.
________________________________
Bruce.Moore@conectiv.com
Sent: Monday, February 25, 2008 10:15 AM
To: Multiple recipients of list ODTUG-SQLPLUS-L
figure dit
I figured out the reason I was having issues with my MERGE statement is
that there were duplicates in my source table
In an effort to clean these up, I realized I was going down the wrong
path, in terms of populating my temporary table
So I figured I'd turn to you guys for guidance
I have a table in my remote system which has a row that contains a
description and a price for each day in a month
In my main system, I want to create one record for the month
So, I want to take anywhere from 28,29,30.31 rows from remote system,
and make it one row for the month in the new system
I know I can just average the price, but how do I compress the daily
rows in to the one month?
Remote table
One 1/1/05 1/1/05 15
Two 1/2/05 1/2/05 15
Three 1/3/05 1/3/05 15
..
N 1/31/05 1/31/05 15
Local table
One 1/1/05 1/31/05 15
[Attachment #3 (text/html)]
<html xmlns:v="urn:schemas-microsoft-com:vml" \
xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns:st1="urn:schemas-microsoft-com:office:smarttags" \
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]--><o:SmartTagType
namespaceuri="urn:schemas-microsoft-com:office:smarttags" name="PersonName"/>
<!--[if !mso]>
<style>
st1\:*{behavior:url(#default#ieooui) }
</style>
<![endif]-->
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
@font-face
{font-family:sans-serif;
panose-1:0 0 0 0 0 0 0 0 0 0;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman";}
a:link, span.MsoHyperlink
{color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:Arial;
color:navy;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
{page:Section1;}
-->
</style>
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>If your source table looks like \
this:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>Key_field, start_date, end_date, \
price<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>And there can be multiple rows with the
same value in key_field, but you want them compressed into one row for each
distinct value in key_field, do this:<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>SELECT key_field, MIN(start_date)
start_date, MAX(end_date) end_date, AVG(price) price<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> FROM \
source_table<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'> GROUP BY \
key_field;<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'>If there are other fields that need to be
included, but can't be averaged. Counted, or summed, a MIN or MAX will often do
the trick.<o:p></o:p></span></font></p>
<p class=MsoNormal><font size=2 color=navy face=Arial><span style='font-size:
10.0pt;font-family:Arial;color:navy'><o:p> </o:p></span></font></p>
<div>
<div class=MsoNormal align=center style='text-align:center'><font size=3
face="Times New Roman"><span style='font-size:12.0pt'>
<hr size=2 width="100%" align=center tabindex=-1>
</span></font></div>
<p class=MsoNormal><b><font size=2 face=Tahoma><span style='font-size:10.0pt;
font-family:Tahoma;font-weight:bold'>From:</span></font></b><font size=2
face=Tahoma><span style='font-size:10.0pt;font-family:Tahoma'>
ml-errors@fatcity.com [mailto:ml-errors@fatcity.com] <b><span style='font-weight:
bold'>On Behalf Of </span></b>Bruce.Moore@conectiv.com<br>
<b><span style='font-weight:bold'>Sent:</span></b> Monday, February 25, 2008
10:15 AM<br>
<b><span style='font-weight:bold'>To:</span></b> Multiple recipients of list \
<st1:PersonName w:st="on">ODTUG</st1:PersonName>-SQLPLUS-L<br>
<b><span style='font-weight:bold'>Subject:</span></b> Please ignore my previous
message eon the MERGE statement - I figure dit</span></font><o:p></o:p></p>
</div>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><o:p> </o:p></span></font></p>
<p class=MsoNormal><font size=3 face="Times New Roman"><span style='font-size:
12.0pt'><br>
</span></font><font size=2 face=sans-serif><span style='font-size:10.0pt;
font-family:sans-serif'>I figured out the reason I was having issues with my
MERGE statement is that there were duplicates in my source table</span></font> <br>
<font size=2 face=sans-serif><span style='font-size:10.0pt;font-family:sans-serif'>In
an effort to clean these up, I realized I was going down the wrong path, in
terms of populating my temporary table</span></font> <br>
<font size=2 face=sans-serif><span style='font-size:10.0pt;font-family:sans-serif'>So
I figured I'd turn to you guys for guidance</span></font> <br>
<font size=2 face=sans-serif><span style='font-size:10.0pt;font-family:sans-serif'>I
have a table in my remote system which has a row that contains a description
and a price for each day in a month</span></font> <br>
<font size=2 face=sans-serif><span style='font-size:10.0pt;font-family:sans-serif'>In
my main system, I want to create one record for the month</span></font> <br>
<font size=2 face=sans-serif><span \
style='font-size:10.0pt;font-family:sans-serif'>So, I want to take anywhere from \
28,29,30.31 rows from remote system, and make it one row for the month in the new \
system</span></font> <br> <font size=2 face=sans-serif><span \
style='font-size:10.0pt;font-family:sans-serif'>I know I can just average the price, \
but how do I compress the daily rows in to the one month?</span></font> <br>
<br>
<font size=2 face=sans-serif><span \
style='font-size:10.0pt;font-family:sans-serif'>Remote table</span></font> <br>
<font size=2 face=sans-serif><span \
style='font-size:10.0pt;font-family:sans-serif'>One 1/1/05 \
1/1/05 15</span></font> <br>
<font size=2 face=sans-serif><span \
style='font-size:10.0pt;font-family:sans-serif'>Two 1/2/05 \
1/2/05 15</span></font> <br>
<font size=2 face=sans-serif><span \
style='font-size:10.0pt;font-family:sans-serif'>Three \
1/3/05 1/3/05 \
15</span></font> <br> <font size=2 face=sans-serif><span \
style='font-size:10.0pt;font-family:sans-serif'>...</span></font> <br>
<font size=2 face=sans-serif><span style='font-size:10.0pt;font-family:sans-serif'>N
1/31/05 1/31/05
15</span></font> <br>
<br>
<font size=2 face=sans-serif><span \
style='font-size:10.0pt;font-family:sans-serif'>Local table</span></font> <br>
<font size=2 face=sans-serif><span \
style='font-size:10.0pt;font-family:sans-serif'>One 1/1/05 \
1/31/05 15</span></font> <br>
<br>
<o:p></o:p></p>
</div>
</body>
</html>
--
For more information on this topic or to become a member, visit our web site at \
http://www.ODTUG.com Be sure to check out our Seriously Practical (SP) Conferences \
coming up this year!
ODTUG is pleased to announce that Kaleidoscope 2008 will be held at The New Orleans \
Sheraton from June 15-19. Keep checking www.ODTUG.com for more details.
--
Author: John Flack
INET: JohnF@smdi.com
Fat City Hosting, San Diego, California -- http://www.fatcity.com
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ODTUG-SQLPLUS-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic