sum on server with group by clause? 1

[anorakgirl]

Hi,

I have a master table and two child tables. I want to show a sum from one child table and detail from the other.

Say I have the following tables:

select product.productid, product.name, product_option.description, sum(order_line.quantity) from
product, product_option, order_line
where product.productid = product_option.productid
and product.productid = order_line.productid
group by product.productid, product.name, product_option.description

I'm using Crystal Reports 9 and I can't see how to do this.

I've tried adding an SQL Expression field of sum(order_line) but I can't see how to get Crystal Reports to add the group by clause to the sql query.

And if I do the sum as a summary field in the report, the totals are wrong because order_line rows are returned more than once when there are several product options.

I can't see how to make crystal do this.

I eventually want to be able to pass in some date paramters so I can limit which rows are summed from order_line, which means creating a view which sums over order_line to base it on won't work.

Thanks for your help!










~

http://www.anorakgirl.co.uk

~

 

[synapsevampire]

Check under your database connection and you'll see Add Command, which allowsforpasting in real SQL as the datasource for the report.

Please rememebrtopost your database type in the future.

-k

 

[anorakgirl]

Ah brilliant, I had no idea you could do that! (New to Crystal Reports and just finding my way around).

Sorry, I didn't think the database type was relevant, MS SQL for info.

Thanks again.

~

http://www.anorakgirl.co.uk

~

 

[synapsevampire]

The database is always relevent to SQL discussions...

Glad it worked out.

-k