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

## 3 replies

Last post Feb 07, 2017 10:00 AM by navneetmitawa

• qwester

None

0 Points

29 Posts

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

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

???

• navneetmitaw...

Contributor

2843 Points

1405 Posts

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

It's very simple you have to used left join between both tables like.

SELECT CUS.CUSID,COUNT(Odr.OderID) as TotalCOUNT

FROM CUSTOMER as CUS Left JOIN ORDERS as Odr on CUS.CUSID = Odr.CUSID

Group by CUS.CUSID;

//For AVG

SELECT CUS.CUSID,COUNT(Odr.OderID) as TotalCOUNT, SUM(Odr.OderAMount)/COUNT(Odr.OderID) as AVGAmount

FROM CUSTOMER as CUS Left JOIN ORDERS as Odr on CUS.CUSID = Odr.CUSID

Group by CUS.CUSID;

Remember to click “Mark as Answer” on the post, if it helps you.
Navneet Kumar Mitawa
• qwester

None

0 Points

29 Posts

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

Thanks for help, but i need to clarify that only one distinct customerid per row, the resultset cannot have a customerid more than once. After adjusting the order of the JOINS and making the first join a left join giving the Customer table precedence, add the group by clause, I still get multiple customerid s.  So that's why I thought a rowcount over function can separate the concerns ????
Qwest

• navneetmitaw...

Contributor

2843 Points

1405 Posts

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

Can you post your result in table format which help me more, what type result you want.

With conditions...

Remember to click “Mark as Answer” on the post, if it helps you.
Navneet Kumar Mitawa