adding values from diff records

[teroy]

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

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]

doesn't sum(o.frequency) work?

 

[swampBoogie]

You could get a sum within a group

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]

thanks for your help guys

 

[teroy]

From swampboogies example i am using

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

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