Results 1 to 12 of 12

Thread: SQL Query

  1. #1
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    Unhappy Unanswered: SQL Query

    I have a table whose structure is like this:

    db_ID PK
    item_no
    qty

    Now I am trying to write a query who should first occuring item but with a calculated column who contains average of all that specific items i.e.
    data example

    db_id Item_no qty
    1 120. 2
    2 120. 3
    3 141 50
    4 141 70

    now i want data display with the help of query in this format

    db_id item_no average
    1 120. 2.5
    3 141 60

    I hope i convey my scenerio.

    any help will be appreciate
    mr_roomi

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    The order of rows in a database is meaningless...but how about

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99([db_id] int, Item_no int, qty decimal(15,2))
    GO
    
    INSERT INTO myTable99([db_id], Item_no, qty)
    SELECT 1, 120, 2  UNION ALL 
    SELECT 2, 120, 3  UNION ALL
    SELECT 3, 141, 50 UNION ALL
    SELECT 4, 141, 70
    GO
    
      SELECT Item_No, MIN([db_Id]) AS MIN_db_id, AVG(qty)
        FROM myTable99
    GROUP BY Item_No
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    SQL Query

    Hi Brett,

    I am trying to create a report. Is this the query to get report in MS Access using sql server as backend?

    SELECT Item_No, MIN([db_Id]) AS MIN_db_id, AVG(qty)
    FROM myTable99
    GROUP BY Item_No

    Thank you for prompt reply
    mr_roomi

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Is the table linked? are you using the QBE Grid?

    You could cut and paste the sql in to a pass thru query...

    All kinds of options...I'd do the pass thru since the server will do the work...

    Code:
      SELECT Item_No, MIN([db_Id]) AS MIN_db_id, AVG(qty) AS AVG_qty
        FROM myTable99
    GROUP BY Item_No
    And I forgot to add a lable for the average....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    sum

    Hi brett,

    I applied that query and it is giving me sum/average of current item_no only not all the records belongs to that item_no.

    What I am doing wrong?
    mr_roomi

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did you run my test?

    Where are you running your code?

    No miracles....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    my whole query

    Below query gives me most recent row's sum not all the items related to that row.
    SELECT AE_CONTRACT.DB_CONTRACT, AE_CONTRACT.BIDS_OPEN_DATE, AE_CONTRACT.LOCATION, AE_CONTRACT.DESCPT, AE_CONTRACT.PRO_FILENO, AE_CONTRACT.IBM_FILENO, BID_ITEM.ITEM_NO, OFF_ITEM.DESCPT, OFF_ITEM.OFF_QUANT, OFF_ITEM.UNITS, OFF_ITEM.OFF_PRICE, [off_price]*[off_quant] AS amount, BID_ITEM.PRICE_WORDS, [price_words]*[off_quant] AS win_bid_amt, Left([NAME1],21) & IIf(IsNull([JOINT_DESC]),"","(JV)") & IIf([NON_RESP]="'Y","(NR)",NZ([NON_RESP]="N","")) AS Name2, BID_TOTAL.RANK_NUMB, AVG(PRICE_WORDS) AS AV <--------------Problem is here---------<<--
    FROM (((AE_CONTRACT INNER JOIN BID_TOTAL ON AE_CONTRACT.DB_CONTRACT = BID_TOTAL.DB_CONTRACT) INNER JOIN BID_ITEM ON (BID_TOTAL.DB_CONTRACT = BID_ITEM.DB_CONTRACT) AND (BID_TOTAL.BID_VENDOR = BID_ITEM.BID_VENDOR) AND (AE_CONTRACT.DB_CONTRACT = BID_ITEM.DB_CONTRACT)) INNER JOIN OFF_ITEM ON (BID_ITEM.ITEM_NO = OFF_ITEM.ITEM_NO) AND (AE_CONTRACT.DB_CONTRACT = OFF_ITEM.DB_CONTRACT)) INNER JOIN VENDOR ON (AE_CONTRACT.GEN_CONTR = VENDOR.DB_VENDOR) AND (BID_ITEM.BID_VENDOR = VENDOR.DB_VENDOR)
    GROUP BY AE_CONTRACT.DB_CONTRACT, AE_CONTRACT.BIDS_OPEN_DATE, AE_CONTRACT.LOCATION, AE_CONTRACT.DESCPT, AE_CONTRACT.PRO_FILENO, AE_CONTRACT.IBM_FILENO, BID_ITEM.ITEM_NO, OFF_ITEM.DESCPT, OFF_ITEM.OFF_QUANT, OFF_ITEM.UNITS, OFF_ITEM.OFF_PRICE, [off_price]*[off_quant], BID_ITEM.PRICE_WORDS, [price_words]*[off_quant], Left([NAME1],21) & IIf(IsNull([JOINT_DESC]),"","(JV)") & IIf([NON_RESP]="'Y","(NR)",NZ([NON_RESP]="N","")), BID_TOTAL.RANK_NUMB
    HAVING (((AE_CONTRACT.DB_CONTRACT)=31356));
    mr_roomi

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    how can you tell?

    Start with something simpler...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    simple query and structure

    Hi,

    There are three tables: db_contract,off_item and bid_item
    no I am extracting data from all three tables which is fine. The problem is I want sum or average of Price_words field which is in bid_item but I need sum of all items available for perticulatr item_no. this query gives me only current row sum or average which is wrong.
    table strcuture
    table: db_contract
    db_id PK
    desc

    table: off_item
    db_id pk
    item_no pk
    qty

    table: bid_item
    db_id pk
    item_no pk
    title pk
    price_word pk
    mr_roomi

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well first....because I took the time to look at it...

    This thing is a mess....

    What's with the GROUP BY and all the functions...

    Not to mention your joins...It could easily be the because of the INNER JOIN your losing data...

    Make them LEFT JOINS Perhaps

    For our gentle readers...

    Code:
        SELECT  AE_CONTRACT.DB_CONTRACT
    	  , AE_CONTRACT.BIDS_OPEN_DATE
    	  , AE_CONTRACT.LOCATION
    	  , AE_CONTRACT.DESCPT
    	  , AE_CONTRACT.PRO_FILENO
    	  , AE_CONTRACT.IBM_FILENO
    	  , BID_ITEM.ITEM_NO
    	  , OFF_ITEM.DESCPT
    	  , OFF_ITEM.OFF_QUANT
    	  , OFF_ITEM.UNITS
    	  , OFF_ITEM.OFF_PRICE
    	  , [off_price]*[off_quant] AS amount
    	  , BID_ITEM.PRICE_WORDS, [price_words]*[off_quant] AS win_bid_amt
    	  , Left([NAME1],21) & IIf(IsNull([JOINT_DESC]),"","(JV)") & IIf([NON_RESP]="'Y","(NR)",NZ([NON_RESP]="N","")) AS Name2
    	  , BID_TOTAL.RANK_NUMB
    	  , AVG(PRICE_WORDS) AS AV
          FROM  AE_CONTRACT 
    INNER JOIN  BID_TOTAL ON AE_CONTRACT.DB_CONTRACT = BID_TOTAL.DB_CONTRACT
    INNER JOIN  BID_ITEM  ON BID_TOTAL.DB_CONTRACT   = BID_ITEM.DB_CONTRAC 
    		     AND BID_TOTAL.BID_VENDOR    = BID_ITEM.BID_VENDOR 
    		     AND AE_CONTRACT.DB_CONTRACT = BID_ITEM.DB_CONTRACT
    INNER JOIN  OFF_ITEM  ON BID_ITEM.ITEM_NO        = OFF_ITEM.ITEM_NO 
    		     AND AE_CONTRACT.DB_CONTRACT = OFF_ITEM.DB_CONTRACT 
    INNER JOIN   VENDOR   ON AE_CONTRACT.GEN_CONTR   = VENDOR.DB_VENDOR 
    		     AND BID_ITEM.BID_VENDOR     = VENDOR.DB_VENDOR
      GROUP BY  AE_CONTRACT.DB_CONTRACT
    	  , AE_CONTRACT.BIDS_OPEN_DATE
    	  , AE_CONTRACT.LOCATION
    	  , AE_CONTRACT.DESCPT
    	  , AE_CONTRACT.PRO_FILENO
    	  , AE_CONTRACT.IBM_FILENO
    	  , BID_ITEM.ITEM_NO
    	  , OFF_ITEM.DESCPT
    	  , OFF_ITEM.OFF_QUANT
    	  , OFF_ITEM.UNITS
    	  , OFF_ITEM.OFF_PRICE
    	  , [off_price]*[off_quant]
    	  , BID_ITEM.PRICE_WORDS
    	  , [price_words]*[off_quant], Left([NAME1],21) & IIf(IsNull([JOINT_DESC]),"","(JV)") & IIf([NON_RESP]="'Y","(NR)",NZ([NON_RESP]="N",""))
    	  , BID_TOTAL.RANK_NUMB
        HAVING AE_CONTRACT.DB_CONTRACT=31356
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Mar 2004
    Location
    Maryland
    Posts
    152

    sum

    Hi Brett,

    I applied code supplied by you. It is again giving me just current line's average only not all the items related to that item_no. should I write some thing like this:

    select db_id,item_no,(select avg(qty) from bid_item as bd where db.db_contract = bid_item.db_contract and bd.item_no=bid_item.item_no)
    from bid_item

    ????
    mr_roomi

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Mr_roomi
    Hi Brett,

    I applied code supplied by you. It is again giving me just current line's average only not all the items related to that item_no. should I write some thing like this:

    select db_id,item_no,(select avg(qty) from bid_item as bd where db.db_contract = bid_item.db_contract and bd.item_no=bid_item.item_no)
    from bid_item

    ????

    That was my point...it's very convoluted...


    What does

    Code:
      SELECT    [db_id]
    	  , MIN(item_no) AS MIN_item_no
    	  , AVG(qty) AS AVG_qty
        FROM    bid_item 
       WHERE    db_contract = DB_CONTRACT=31356
    GROUP BY   [db_id]
    Give you?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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