Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Include Group and Non group columns in same query
In article <d11ffcbb.0202250905.4ae3c9eb_at_posting.google.com>,
lalithprakash_at_hotmail.com says...
>
>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
>
Select count(*) Match_count, sku, max(ph.desc) some_words_matched
from product_header ph
where (ph.desc = 'vcr' or ph.desc = 'tape' ph.desc = 'video')
group by sku
order by match_count desc
>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(*)
-- Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Tue Feb 26 2002 - 10:48:49 CST