-
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.
-
Try it once and see what you get.
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?
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
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...
And the query you have been working on is...
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|