Re: Question on Query

On Nov 26, 12:51 pm, m..._at_mtekusa.com wrote:
> On Nov 26, 12:27 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net>
> wrote:
>
>
>
>
>
> > On Nov 26, 12:24 pm, m..._at_mtekusa.com wrote:
>
> > > 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 -
>
> > > Hmmm...seems to be complaining just a bit:
>
> > > SQL> 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', 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),
> > > name
> > > END)
> > > ORDER BY 1;
>
> > > AS 'PRODUCT ID', COUNT(*) AS ACTIVE
> > > *
> > > ERROR at line 8:
> > > ORA-00923: FROM keyword not found where expected- Hide quoted text -
>
> > > - Show quoted text -
>
> > Single quotes delimit text, double quotes delimit column/object names.
>
> > David Fitzjarrell
>
> Thanks David! I will be a pest one more time. Is it possible to get
> this output, or do you think it is time for a PLSQL routine:
>
> PRODUCT ID PRODUCT NAME ACTIVE
>
> (2, 109, 124, 144) ZA Web 1YR + ZER 76
> . ZA Web 3YR + ZER 1001
> . ZA Web 5YR + ZER 270
> . ZA Web QTR + ZER 4
> 10 Product ABC
> 14 Product DEF
> 22 Product GHI- Hide quoted text -
>
> - Show quoted text -

I think you can modify the query somewhat to produce similar output. You'll need to work with it. You should have all of the tools you need in the examples and your current query text.

David Fitzjarrell Received on Mon Nov 26 2007 - 12:55:24 CST