One CustomerID in a report that combines different order IDs, aggregates problem

1

I hav a series of tables that generates order and delivery information, ultimately i am needing to generate a report that will have one distinct customerID list a series of orderIDs that will calculate a mixture of count(*) and avg()

SELECT o.OrderID,o.customerid,cu.city
[AverageAmount] = convert(VARCHAR,convert(MONEY,AVG(orderamount)
OVER(PARTITION BY o.customerid ),
1)),
[TotalAmount] = convert(VARCHAR,convert(MONEY,SUM(orderamount)
OVER(PARTITION BY o.customerid ),
,ROW_NUMBER() OVER(PARTITION BY ode.datebegin ORDER BY o.customerid) AS seq

FROM Orders o join orderdetails ode on o.OrderID = ode.OrderID
join customers cu on cu.customerid = o.customerid

where ode.datebegin between getdate() -7 and getdate()

...this doesnt give me just one distinct customerid resultset , instead multiple rows for each customerid if there are multiple orderid

???
Thanks in advance

2

Probably an obvious answer, but I'm not seeing it: Why all the PARTITION stuff rather than just do a GROUP BY on the main query?

Suggestions:

convert(VARCHAR, convert(MONEY, AVG(orderamount) ...

Don't use VARCHAR without a size. The default is usually suitable, but is never an unlimited size and can be 1 :frowning:

where ode.datebegin between getdate() -7 and getdate()

This will give you a "date range" based on the current date AND TIME. If you actually want the last 7 days, inclusive of whole days, then it needs "rounding" to whole days.

3

Thanks, but using groupby doesnt limit just one customerid per resultset, i still get a few of the same customerids; the report needs distinct customerid , and even using DISTINCT customerid does not render unique customerids
?:??
S

4

Do you mean in your (obviously "complex") report above?, or even if just you do a simple

SELECT DISTINCT customerid FROM MyTable ORDER BY customerid

do you also get "duplicates" in that? If so there are some "weird" characters in the data which are not showing up, visibly, on screen but are causing some values to be seen as being "different" for example:

SELECT DISTINCT ']' + customerid + '['
FROM
(	SELECT [customerid] = 'FRED'
	UNION ALL SELECT 'FRED '
	UNION ALL SELECT ' FRED'
	UNION ALL SELECT ' FRED '
) AS X

If that's all that it is then get the data cleaned up :slight_smile:

If it is only happening on a more complex report than either the customerid has "weird characters" (if so then the simple SELECT DISTINCT will also show them) or one of the other tables has multiple values

5

I'll probe more into the tables and isolate some data with hand-fed queries, I'll perhaps come back with some DDL and visual screenshots of how I need "distinct" values.
Thanks
SQ17