Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Include Group and Non group columns in same query

Re: Include Group and Non group columns in same query

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 26 Feb 2002 08:48:49 -0800
Message-ID: <a5ge9h02fv9@drn.newsguy.com>


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 Corp 
Received on Tue Feb 26 2002 - 10:48:49 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US