adding values from diff records

teroy · Apr 10, 2003

hi guys..
i have a select statement which goes like this

Code:

SELECT l.id,o.frequency,o.frequency as freq1, l.name, l.url, l.description FROM links l, object_word_link o, word w WHERE l.id=o.object_id AND w.id=o.word_id AND ( lower(l.name || l.keywords) like '%melbourne%' AND lower(l.name || l.keywords) like '%arts%' ) AND ( lower(w.word) = 'melbourne') AND ( ( w.object_count / 7366 ) < 0.7 )
intersect
SELECT l.id,o.frequency, o.frequency as freq2,l.name, l.url, l.description FROM links l, object_word_link o, word w WHERE l.id=o.object_id AND w.id=o.word_id AND ( lower(w.word) = 'arts') AND ( ( w.object_count / 7366 ) < 0.7 )
intersect
SELECT l.id,o.frequency, o.frequency as freq3,l.name, l.url, l.description FROM links l, object_word_link o, word w WHERE l.id=o.object_id AND w.id=o.word_id AND ( lower(w.word) = 'citysearch') AND ( ( w.object_count / 7366 ) < 0.7 )

Basically this returns nearly what i want.
The only extra thing is i want to be able to do is add the frequency field of the 3 selects and return it. At the moment it returns the frequency field for the 1st select.

eg. if the frequency field for the 3 resulting selects are
1,3,4 i would like it to return 8 instead of returning 1

Thanks in advance

newbiepg · Apr 11, 2003

doesn't sum(o.frequency) work?

swampBoogie · Apr 12, 2003

You could get a sum within a group

Code:

SELECT sum(o.frequency),
       l.id,  l.name, l.url, l.description 
  FROM links l, object_word_link o, word w 
 WHERE l.id=o.object_id 
   AND w.id=o.word_id 
   AND w.object_count/7366 < 0.7
   AND (( lower(l.name || l.keywords) like '%melbourne%' 
   AND lower(l.name || l.keywords) like '%arts%' 
   AND lower(w.word)  = 'melbourne' )
    or ( lower(w.word) in ('arts','citysearch') ))
l.id,  l.name, l.url, l.description

Intersect will return records that are present in all results, so I don't see that it is applicable in this context.

teroy · Apr 13, 2003

thanks for your help guys

teroy · Apr 14, 2003

From swampboogies example i am using

Code:

SELECT sum(o.frequency),
       l.id,  l.name, l.keywords
       FROM links l, object_word_link o, word w
       WHERE l.id=o.object_id
       AND w.id=o.word_id
       AND w.object_count/7366 < 0.7
       AND ( lower(w.word) in ('melbourne','citysearch') )
       group by l.id,  l.name, l.keywords
       order by sum(o.frequency) DESC;

This sums up the frequency and orders by it no worries.

The only problem is that i would like it to order by the word matches first then frequency..

eg. If the words melbourne and citysearch were found in the grouped record then it would order higher than melbourne alone.

Is this possible with sql?

here is a snippet of tables

Code:

word
  id   |    word    | object_count
-------+------------+--------------
   291 | melbourne  |          275
 10241 | citysearch |           13



links
  id   |                     name                     |                                url
-------+----------------------------------------------+-------------------------------------------------------------------
    7133 | Citysearch food and wines                    | [URL unfurl="true"]http://melbourne.citysearch.com.au/Melbourne/Food_Wine/[/URL]
 13635 | CitySearch Melbourne Professionals & Careers | [URL unfurl="true"]http://melbourne.citysearch.com.au/section/professionals_careers/[/URL]
 13634 | CitySearch Melbourne Office & Technology     | [URL unfurl="true"]http://melbourne.citysearch.com.au/section/office_technology/[/URL]
 13632 | CitySearch Melbourne House & Garden          | [URL unfurl="true"]http://melbourne.citysearch.com.au/section/house_garden/[/URL]
 13633 | CitySearch Melbourne Motoring                | [URL unfurl="true"]http://melbourne.citysearch.com.au/section/motoring/[/URL]
 13631 | CitySearch Melbourne Health & Leisure        | [URL unfurl="true"]http://melbourne.citysearch.com.au/section/health_leisure/[/URL]
 13637 | CitySearch Melbourne Visitor Guide           | [URL unfurl="true"]http://melbourne.citysearch.com.au/section/visitor-guide/[/URL]
 13629 | CitySearch Melbourne Arts                    | [URL unfurl="true"]http://melbourne.citysearch.com.au/section/the-arts[/URL]


object_word_link 
   id   | object_id | word_id | frequency
-------+-----------+---------+-----------
 42030 |     13629 |   10241 |       0.2
 42031 |     13629 |     291 |       0.2

Thanks in Advance

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

adding values from diff records

teroy

Programmer

newbiepg

Programmer

swampBoogie

Programmer

teroy

Programmer

teroy

Programmer

Similar threads

Part and Inventory Search

Sponsor