asked on

Oracle group by using string

select pgm.program_name ,SUM(SI.QUANTITY - si.QTY_INVOICED) as BOCount,SUM((SI.QUANTITY - si.QTY_INVOICED)* si.UNIT_COST) as BOCost  
from  FACT_SALESITEM si
inner join  DIM_NAME_VALUES hdr on  hdr.DIM_NAME_VALUES_ID = si.DIM_NV_HDR_STATUS_ID
inner join  DIM_NAME_VALUES line on  line.DIM_NAME_VALUES_ID = si.DIM_NV_LINE_STATUS_ID
left join DIM_PRODUCT prod on si.dim_product_id = prod.DIM_PRODUCT_ID
INNER JOIN DIM_PROGRAM pgm ON si.dim_program_id = pgm.DIM_PROGRAM_ID
where 
pgm. program_name like 'TSI%'
and hdr.value_code <> 'SC_CN' and hdr.value_code <> 'SC_ALLOC'
group by PROGRAM_name

Open in new window

The above query returns me the aggrgation of qty and sum for grouping of any program that has 'TSI'.Is there any way I can modify the query so that it aggregates the qty and cost for all the program names that have TSI to one single row

SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Star79

ASKER

Both the above solutions work.Which do you think is more efficient coding

slightwv (䄆 Netminder)

>>Which do you think is more efficient coding

It is about the data you need.

I'm not sure there is a measurable difference.

If Oracle: generate an explain plan for both. I'm betting there isn't a difference.

Not sure if SQL Server has anything like the explain plan or not.

Star79

ASKER

This is a oracle question Sir!

Qlemo

If you want to get a single row only, and not group by the first three characters dynamically, my solution is better, because it eliminates the use of a function. The difference should not be significant, though, unless we are talking 'bout millions of rows. It is an in-memory operation anyway, but the function might require Oracle to use the temp tablespace for bigger result sets.

If you want the query to work even if you remove the pgm. program_name like ..., you'll have to go with slightwv's suggestion, and get several rows grouped by the first three characters that way.