Need help to write the Query

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.

Quote:

---

Originally Posted by hiteshkataria

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

---

1) Use the "code" tags (# icon).
2) This looks like homework, what have you tried so far?
:rolleyes:

I'm Sorry..

The data is as below:

Code:

---

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

---

The Ouptput needs to be:

Code:

---

Name        Count-Item        Item-Cost        Count-Sold        Item-Sold        Count-Return        Item-Return
A1        5                33                2                23                2                11
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.

And the query you have been working on is...:confused:

I just treid for initial 3 field and could not think of rest.

This is what I tried:

Code:

---

SELECT Name, Count(Cost), Sum(Cost)
FROM ItemTab
Group  by Name
ORDER BY Name;

---

Specs are asking for counting items on each group, not cost.
Query still needs some work but you are in the right path - keep going.