writing a count for a distict field to a new table

[collierd]

Hello

I am having real problem getting my head round how to perform a count of a distinct field and writing this as a field into a new table

I have a table (orders)
This table has various fields but, more relevant to the problem:

date (yyyymm only)
branch,
order_number,
order_line,
order_line_count

where a example of an order number could be 'a12' and the order line could be 'a12*1', 'a12*2', 'a12*3' etc dependant on the number of lines

I want to create a new summarised table (order count) which will be made up of:

order_date
branch
order_count
order_line_count

order_line_count is fairly straight forward as there is one instance of an order line for every record in the orders table (the table orders has a field called order_line_count which just contains 1 so this can be summed)

order_count proves more tricky as this needs to record 1 for ever distinct instance of an order and summarise this at date/branch level

This is an example of the starting code where ??? needs filling in with the order count

insert into invoiced_order_count
select invoice_date, branch_code, ???,
sum (order_line_count)
from orders
group by invoice_date, branch_code


Does it require a temporary table to store the order count in? If so how?

Any ideas would be much appreciated

Thanks

Damian.

 

[fluteplr]

try

count(distinct order_number)

where you have ???

 

[collierd]

Thanks - worked a treat