Hi,

I need your expertise in writing the Query for the below requirement.

A table contains the below fileds and data:



Name Cost Sold-Amt Sold-bill
A1 5 8 22
B2 1
A1 8 15 21
C3 5 10 20
A1 7 8 R2
A1 8
A1 5 3 D2
B2 3 2 18
B2 3
C3 1

Whenver the Item gets sold, Sold-Amt and Sold-bill will have the data.
(If you notice, some of the data in Sold-bill contains value starting with R or D, which means the Item has been returned to the supplier).

Now my requirement is write a Query to get the Output as below:

Name Count-Item Item-Cost Count-Sold Item-Sold Count-Return Item-Return
A1 5 33 2 23 2 12
B2 3 7 1 2 0 0
C3 2 6 1 10 0 0

Field-1 Name - Grouped and Ordered
Field-2 Count-Item - Count of Items
Field-3 Item-Cost - Sum of Cost of Items
Field-4 Count-Sold - Count of Items which are Sold
Field-5 Item-Sold - Sum of Sold-Amt which are Sold
Field-6 Count-Return - Count of Items which are Returned (starting with R or D)
Field-7 Item-Return - Sum of Cost of Items which are Returned.

The data might be confusing due to Tab/Space issues. Sorry for that.