Help with SQL Query

[mrmmickle1]

I am trying to turn this dataset:

Excel 2012
	A	B	C	D	E	F
1	OrderNo	ID	Qty	UOM	UnitPrice	Total
2	MRM-100002	Loc002779	1	EA	72.25	72.25
3	MRM-100002	Loc002779	1	EA	3.09	3.09
4	MRM-100002	Loc002779	1	EA	8.13	8.13
5	MRM-100003	Loc002717	800	EA	0.13	104
6	MRM-100003	Loc002717	30	PK	2.3	69
7	MRM-100003	Loc002717	30	PK	6.84	205.2
8	MRM-100003	Loc002717	10	EA	8.13	81.3
9	MRM-100003	Loc002717	2	EA	10.68	21.36
10	MRM-100005	Loc002790	200	EA	0.1	20
11	MRM-100005	Loc002790	150	EA	0.43	64.5
12	MRM-100006	Loc002779	100	EA	2.08	208
13	MRM-100006	Loc002779	100	EA	0.43	43
14	MRM-100006	Loc002779	20	EA	13.07	261.4
Sheet1

Into this:

Excel 2012
	H	I	J
1	ID	OrderCount	TotalCost
2	Loc002779	2	595.87
3	Loc002717	1	480.86
4	Loc002790	1	84.5
Sheet1

By Using an SQL Query. I am unfamiliar with this calculation type query. Any help would be much appreciated.

TotalCost is calculated by summing up the Qty X UnitPrice for each Line Grouped by location.

OrderCount is calculated by counting distinct OrderNo for each ID

 

[Joe4]

I would do this in a two step process (two queries).

The first query would group by ID and OrderNo and Sum the Total amounts.
The SQL code for that query would look like this:

SELECT DataTable.ID, DataTable.OrderNo, Sum(DataTable.Total) AS SumOfTotal
FROM DataTable
GROUP BY DataTable.ID, DataTable.OrderNo;

Let's call this query "SubTotalQuery".

Now, we can use this query in another Aggregate Query to get what you want. On this one, we Group By ID, count the OrderNo field and sum the Total field.
That SQL code would look something like this:

SELECT SubTotalQuery.ID, Count(SubTotalQuery.OrderNo) AS OrderCount, Sum(SubTotalQuery.SumOfTotal) AS TotalCost
FROM SubTotalQuery
GROUP BY SubTotalQuery.ID;

and this returns what you want (shown in your original post).

 

[mrmmickle1]

Joe4,

You nailed it!! It was a little more complex than I made out but, your guidance surely got me to the end result:

[COLOR=#0000ff]SELECT[/COLOR] SUBTOTAL.ID, Count(SUBTOTAL.OrderNo) [COLOR=#0000ff]AS[/COLOR] OrderCount, Sum(SUBTOTAL.SumOfTotal) [COLOR=#0000ff]AS[/COLOR] ProductCost
[COLOR=#0000ff]FROM [/COLOR]([COLOR=#0000ff]SELECT [/COLOR]INNERQUERY.ID , INNERQUERY.OrderNo, Sum(INNERQUERY.Total) [COLOR=#0000ff]AS[/COLOR] SumOfTotal
[COLOR=#0000ff]FROM[/COLOR]
([COLOR=#0000ff]SELECT[/COLOR] dbo_OrderNoLine.OrderNo,  dbo_OrderNo.ID, dbo_OrderNoLine.Qty, dbo_OrderNoLine.UOM, dbo_OrderNoLine.UnitPrice, dbo_OrderNoLine.UnitPrice*dbo_OrderNoLine.Qty [COLOR=#0000ff]AS [/COLOR]Total
[COLOR=#0000ff]FROM[/COLOR] dbo_OrderNo[COLOR=#0000ff] INNER JOIN[/COLOR] dbo_OrderNoLine [COLOR=#0000ff]ON[/COLOR] dbo_OrderNo.[OrderNo] = dbo_OrderNoLine.[OrderNo]
[COLOR=#0000ff]WHERE[/COLOR] (((dbo_OrderNo.Status)="Completed" [COLOR=#0000ff]AND[/COLOR] (dbo_OrderNo.ID) <> "" [COLOR=#0000ff]AND [/COLOR](dbo_OrderNo.ID) <> "Loc000999" ))) [COLOR=#0000ff]AS [/COLOR]INNERQUERY
[COLOR=#0000ff]GROUP BY[/COLOR] INNERQUERY.ID, INNERQUERY.OrderNo)  AS SUBTOTAL
[COLOR=#0000ff]GROUP BY[/COLOR] SUBTOTAL.ID;

 

[Joe4]

Great! Glad to hear it!

I was going to tell you that you could do it all in one query if you nest them, but I see that you already figured that part out.