Re: Question on Query

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