Need help to write the Query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Need help to write the Query

  1. #1
    Join Date
    Feb 2010
    Posts
    6

    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.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Cool Try it once and see what you get.

    Quote Originally Posted by hiteshkataria View Post
    ... 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

  3. #3
    Join Date
    Feb 2010
    Posts
    6
    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.

  4. #4
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Cool 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

  5. #5
    Join Date
    Feb 2010
    Posts
    6
    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;

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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
  •  



Click Here to Expand Forum to Full Width