asked on

SQL- GROUP BY

Hello,
can you please help,
I need one statement instead of below 2 and get one value for each,
right now, I get 2 value for each operator.

Select Operator AS [Operator],Convert (Varchar(11), ao.OrderDate,113) AS [Date],Count(Orderno) AS [Count]
FROM Activeorders ao
WHERE orderdate >= dateadd(dd,-14,datediff(dd,0,getdate())) and orderdate < dateadd(dd,0,datediff(dd,0,getdate()))
Group By ao.Operator, Convert (Varchar(11), ao.OrderDate,113)
UNION ALL
Select Operator AS [Operator] ,Convert (Varchar(11), fo.OrderDate,113) AS [Date],Count(Orderno) AS [Count]
FROM finalizedorders fo
WHERE orderdate >= dateadd(dd,-14,datediff(dd,0,getdate())) and orderdate < dateadd(dd,0,datediff(dd,0,getdate()))
Group By fo.Operator, Convert (Varchar(11), fo.OrderDate,113)
Order by [Operator], [Date]

Thank you,

JesterToo

Please show a sample of the input data, what you're currently getting for output, and an example of what you want for output.

W.E.B

ASKER

Hi Jester,
Result
Operator      Date            Count
User 1            03 Apr 2017      2
User 1            03 Apr 2017      28
User 1            04 Apr 2017      1
User 1            04 Apr 2017      40
User 1            05 Apr 2017      1
User 1            05 Apr 2017      17

would like to have
Operator      Date            Count
User 1            03 Apr 2017      30
User 1            04 Apr 2017      41
User 1            05 Apr 2017      18

ASKER CERTIFIED SOLUTION

JesterToo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

W.E.B

ASKER

Thank you very much.