Stack Exchange
SQL Query SELECT and Group by with conditions
I have a table called item table.
Sample contents:
order           product    plan qty price   term    base_price
CO0300039921    ZZFEE0000   N   1   0.01    1       25
CO0300039921    ZZFEE0000   N   1   0.01    1       37.13
CO0300039921    ZZFEE0000   N   1   0.02    1       37.13
CO0300039921    ZZFEE0000   Y   1   0       1       1
CO0300039921    ZZFEE0000   Y   1   0       1       1
AO1000301407    VOSVC0002W0 Y   1   0       1       3
AO1000301407    VOACT0101   N   1   0       2       5.99


If plan is ‘N’ Then
    get SUM(qty) AS 'quantity'
    get price AS 'rate'
If plan is ‘Y’ Then
    get SUM(qty) AS 'quantity'
    get (term * qty) AS 'rate'
    get (base_price) AS 'base'

FROM item
WHERE order = 'CO0300039921'
GROUP BY product, price, base_price

EXPECTED RESULT:
 order           product    plan qty  price rate  base_price  
    CO0300039921    ZZFEE0000   N   2    0.01  0.01     -
    CO0300039921    ZZFEE0000   N   1    0.02  1        -
    CO0300039921    ZZFEE0000   Y   2    -     1        1 

Here is what I've tried:
SELECT CASE WHEN p8_plus_plan = 'Y' THEN 
( 

   SUM(qty_ordered),
   (p8_contract_term*qty_ordered) AS 'rate',
   product_base_price
)
ELSE 
( 
   SUM(qty_ordered),
   price AS 'rate'
)
END
FROM ns_order_line 
WHERE order_no = ? 
GROUP BY product_id, price, p8_plus_plan, product_base_price, order_no; 

But I'm having an error. Please help.

why do you have so many databases tagged?

You can start your query with SELECT and use CASE WHEN ... THEN ... ELSE ...

Please edit your question and show the results that you want.

@GordonLinoff Added expected result

Something like this I imagine.
SELECT
      `order`
    , `product`
    , `plan`
    , CASE WHEN `plan` = 'Y' THEN base_price END AS base /* edit due to comment */
    , SUM(qty) AS qty
    , MAX(price) AS price
    , SUM(CASE WHEN `plan` = 'N' THEN price
               WHEN `plan` = 'Y' THEN (term * qty) END) AS rate
FROM item
WHERE `order` = 'CO0300039921'
GROUP BY
      `order`
    , `product`
    , `plan`
    , CASE WHEN `plan` = 'Y' THEN base_price END

btw: The column name "order" is not a good choice as it is a reserved word and very frequently used too. You need to use backticks when referencing that column.
It is possible to use a CASE EXPRESSION without an aggregate function, BUT that case expression then must* also be part of he GROUP BY clause. 
*In MySQL (only) you can bypass this because MySQL has an unusual "extension" to group by syntax but this can be changed by server settings and relying on that extension is risky. I strongly urge you to include all columns (including case expressions) into the group by clause that do NOT use an aggregate function (sum/count/avg etc.) 

the base_price should not be a sum... can I have  a case without a sum?

yes you can, I made changes to the answer due to that comment

You would do this using conditional aggregation.  Something like this:
select sum(case when plan = 'N' then qty end) as N_qty,
       sum(case when plan = 'N' then price end) as N_rate,
       sum(case when plan = 'Y' then qty end) as Y_qty,
       sum(case when plan = 'Y' then term*qty end) as Y_rate,
       sum(case when plan = 'Y' then base_price end) as Y_base
from item
where order = 'CO0300039921'
group by product;

I'm not sure what you really want to aggregate by.  But the key idea where is the case inside the aggregation functions.

I've added the expected result
