Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2010
    Posts
    22

    Unanswered: Help for SQL query

    I have data as mentioned below:
    Code:
    Code	Cost	Price	SoldBill
    A	100	120	22
    A	20	30	D5
    A	30	45	
    A	5	10	
    B	50	70	23
    B	40	60	32
    B	30	45	
    C	35	50	D2
    C	25	40
    I need a report from this data as below:

    Code:
    Code	P_Qty	P_Cost	S_Qty	S_Price	Re_Qty	Re_Cost	St_Qty	St_Cost
    A	4	155	1	120	1	20	2	35
    B	3	120	2	130	0	0	1	30
    C	2	60	0	0	1	35	1	25
    Data should be GROUPed by Code
    P_Qty - Purchase Qty: Count of a particular Code items
    P_Cost - Purchase Cost: Sum of Cost of all group items

    S_Qty - Sold Qty: Count of grouped Items Sold (which are having SoldBill not starting with D and not blanks)
    S_Price - Sold Price: Sum of grouped Price (which are having SoldBill not starting with D and not blanks)

    Re_Qty - Return Qty: Count of grouped Items (which are having SoldBill starting with D)
    Re_Cost - Return Cost: Sum of grouped Cost (which are having SoldBill starting with D)

    St_Qty - Stock Qty: Count of grouped Items (which are having SoldBill as blank)
    St_Cost - Stock cost: Sum of grouped Cost (which are having SoldBill as blank)

    Please suggest a query.

    Thanks.

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    One should not look at it in terms of a single query. You need to divide this into tasks - and then reconstruct it for display.

    Make a query to result in the values that are correct for each individual column/field. (depending on calculation/field you may be able to do multiple calculations in the same query)

    This will leave you a series of queries.

    then look to group and join these queries to bring the data back together for display.

    also remember that if you use a Report - it has a grouping feature too.

    Hope this helps a little,
    www CahabaData com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •