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

Home -> Community -> Usenet -> c.d.o.server -> Re: Question on Query

Re: Question on Query

From: <mtek_at_mtekusa.com>
Date: Mon, 26 Nov 2007 09:55:13 -0800 (PST)
Message-ID: <49795ea9-3365-4dd3-99e7-f597a93c8fca@e6g2000prf.googlegroups.com>


On Nov 26, 11:45 am, jimmyc <jmch..._at_netscape.net> wrote:
> Try using a Case Statement. That should work for your problem.
>
> Here's an example.. from the Oracle DOCS...
>
> SELECT (CASE WHEN cust_credit_limit BETWEEN 0 AND 3999 THEN ' 0 -
> 3999'
> WHEN cust_credit_limit BETWEEN 4000 AND 7999 THEN ' 4000 - 7999'
> WHEN cust_credit_limit BETWEEN 8000 AND 11999 THEN ' 8000 -
> 11999'
> WHEN cust_credit_limit BETWEEN 12000 AND 16000 THEN '12000 -
> 16000' END)
> AS BUCKET, COUNT(*) AS Count_in_Group
> FROM customers WHERE cust_city = 'Marshal' GROUP BY
> (CASE WHEN cust_credit_limit BETWEEN 0 AND 3999 THEN ' 0 - 3999'
> WHEN cust_credit_limit BETWEEN 4000 AND 7999 THEN ' 4000 - 7999'
> WHEN cust_credit_limit BETWEEN 8000 AND 11999 THEN ' 8000 - 11999'
> WHEN cust_credit_limit BETWEEN 12000 AND 16000 THEN '12000 - 16000'
> END);
>
> BUCKET COUNT_IN_GROUP
> ------------- --------------
> 0 - 3999 8
> 4000 - 7999 7
> 8000 - 11999 7
> 12000 - 16000 1
>
> On Nov 26, 12:27 pm, m..._at_mtekusa.com wrote:
>
> > On Nov 26, 11:24 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net>
> > wrote:
>
> > > On Nov 26, 11:03 am, m..._at_mtekusa.com wrote:
>
> > > > On Nov 26, 9:29 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> > > > > On Nov 26, 8:55 am, m..._at_mtekusa.com wrote:
>
> > > > > > Hello,
>
> > > > > > I have the following query:
>
> > > > > > SELECT p.product_id "PRODUCT ID", p.name "PRODUCT NAME", count(*)
> > > > > > "ACTIVE"
> > > > > > FROM customer.subscriptions s, customer.product p
> > > > > > WHERE p.product_id = s.product_id AND exp_date > SYSDATE
> > > > > > GROUP BY p.product_id, p.name
> > > > > > ORDER BY p.product_id;
>
> > > > > > This works fine. However, what I need to do is SUM certain product
> > > > > > numbers together and not others. So, if I have the following data:
>
> > > > > > PRODUCT ID
> > > > > > 1
> > > > > > 2
> > > > > > 3
> > > > > > 4
> > > > > > 5
>
> > > > > > I want to sum product ID's 1 + 3, and the others will not be summed.
> > > > > > Can this be done in a query? Maybe with DECODE?
>
> > > > > > Thanks everyone!
>
> > > > > Your description is a bit 'sketchy' as to what, exactly, you want, as
> > > > > I'm fairly certain that your 'example' isn't completely defined. Nor
> > > > > do you supply any Oracle release information (all four numbers),
> > > > > necessary to provide a proper answer. What criteria are you using to
> > > > > decide which product_id values to combine into subtotals? There may
> > > > > be a number of 'solutions' to your problem but with the sparse
> > > > > information you've provided supplying a usable suggestion is
> > > > > difficult, at best.
>
> > > > > Provide the requested data (Oracle release to 4 numbers, a better
> > > > > problem description) and possibly someone can provide some information
> > > > > you can use.
>
> > > > > David Fitzjarrell
>
> > > > We are running Oracle 10g R2.
>
> > > > In the query, the criteria is very simple, I'm just joining 2 tables
> > > > based on Product ID. But, certain Product ID's I want to combine
> > > > together with regards to the COUNT(*).
>
> > > > Can this be done with a query???- Hide quoted text -
>
> > > > - Show quoted text -
>
> > > HOW do you decide these 'certain' product_id values? Are the
> > > product_name values the same, or similar? Computing the sum of these
> > > totals is fairly simple, it's the reporting of these sums which may be
> > > a bit involved, as you haven't specified how you want those aggragates
> > > displayed.
>
> > > David Fitzjarrell
>
> > So, I have 10 products:
> > 1
> > 2
> > 3
> > 4
> > 5
> > 6
> > 7
> > 8
> > 9
> > 10
>
> > And I want to combine the counts of 1,3,4 and 7,9 and the rest stay
> > the same as single counts for that product...........- Hide quoted text -
>
> > - Show quoted text -

Thanks for the starter query. This is close, but no cigar yet:

SELECT
  (CASE
    WHEN produst_id IN (2, 109, 124, 144) THEN 'ZA Web 1YR + ZER'     WHEN produst_id IN (103, 110, 111, 125, 145) THEN 'ZA Web 3YR + ZER'
    WHEN produst_id IN (119, 126) THEN 'ZA Web 5YR + ZER'     WHEN produst_id IN (106, 127) THEN 'ZA Web QTR + ZER' END)    AS 'PRODUCT ID', name AS 'PRODUCT NAME', COUNT(*) AS ACTIVE FROM product.subscriptions s, customer.product p WHERE p.produst_id = s.produst_id AND exp_date > SYSDATE GROUP BY
  (CASE
   WHEN produst_id IN (2, 109, 124, 144) THEN 'ZA Web 1YR + ZER'    WHEN produst_id IN (103, 110, 111, 125, 145) THEN 'ZA Web 3YR + ZER'
   WHEN produst_id IN (119, 126) THEN 'ZA Web 5YR + ZER'    WHEN produst_id IN (106, 127) THEN 'ZA Web QTR + ZER' END) END); I'll play with it a bit and see what I can find...... Received on Mon Nov 26 2007 - 11:55:13 CST

Original text of this message

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