I would like to report a single row for each 'cardcode' (I know there is only one at the moment) but as each row already has a 'sum' calculation in it I am not sure how to do this, any ideas please?
The idea is to report only a total sales value per customer on one row for the given time period
SELECT
t1.cardcode as 'Code',
t1.cardname as 'Name',
t1.docnum as 'Document Number',
sum(t0.price) as 'Sales Value'
FROM
rdr1 t0
inner join ordr t1 on t0.docentry = t1.docentry
WHERE
t1.docdate >= '[%0]' and
t1.docdate <= '[%1]' and
t1.cardcode = 'ALL006'
GROUP BY
t1.cardcode,
t1.cardname,
t1.docnum
Thanks,
Robin