Need help to write the Query

[hiteshkataria]

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.

[LKBrwn_DBA]

... 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?

[hiteshkataria]

I'm Sorry..

The data is as below:

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:

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.

[LKBrwn_DBA]

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

[hiteshkataria]

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

This is what I tried:

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

[PAVB]

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.