Re: Include Group and Non group columns in same query

From: Mike <nospam_at_nowhere.net>
Date: Tue, 26 Feb 2002 11:19:19 +1100
Message-ID: <VuAe8.6$nA1.14469@vicpull1.telstra.net>

You could try this:

Select

    count(*) Match_count
  , sku

  , decode(sum(decode(sign(instr(ph.desc,'vcr')),0,0,1)),0,null,'vcr ') ||
    decode(sum(decode(sign(instr(ph.desc,'tape')),0,0,1)),0,null,'tape ') ||
    decode(sum(decode(sign(instr(ph.desc,'video')),0,0,1)),0,null,'video')

words_matched
from product_header ph
where (ph.desc = 'vcr' or ph.desc = 'tape' or ph.desc = 'video') group by sku
order by match_count desc

Not very nice when its hard coded through.

Mike

"Prakash Pattisapu" <lalithprakash_at_hotmail.com> wrote in message news:d11ffcbb.0202250905.4ae3c9eb_at_posting.google.com...
> Hi,
> Is there a way to include group and non group columns in the same
> sql?.
> I have the following sql..
>
> Select count(*) Match_count, sku from product_header ph
> where (ph.desc = 'vcr' or ph.desc = 'tape' ph.desc = 'video')
> group by sku
> order by match_count desc
>
>
> The above query will give me the number of occurences of the words
> 'vcr', 'tape' and 'video' for each sku.
> ex: match_count sku
> 3 100
> 2 101
> 2 102
> 1 103
>
> My problem is, i also want the "words" that matched in the result set.
> Like this...
> ex: match_count sku words_matched
> 3 100 vcr tape
> 2 101 vcr video
> 2 102 tape video
> 1 103 tape
>
> Since, ph.desc is not a group by column, i can't include it in the sql
> directly.
> Is there a way to achieve the above result set without looping thru
> the outer query (The main group by query). To put it straight, can we
> do this in one sql?.
> Thanks so much,
> Prakash
>
>
> select count(*)
Received on Mon Feb 25 2002 - 18:19:19 CST