Professional Web Applications Themes

Multi table queries with agregates - Microsoft SQL / MS SQL Server

I have a master part list table and a in stock table with an entry for each part. I want a result set from the master table with a count for the parts in the stock table. I tried this query: select m.Part_no, m.Man, m.Model_Application, m.Years, m.Disp, m.description, m.catalog_comments, m.style, m.sugg_ret, (count(b.part_no)) onhand from makemodel m, bin_list b where m.part_no = b.part_no group by m.part_no, b.part_no and I ger the error: When GROUP BY exists, every simple field in projectors must be in GROUP BY. I have tried several variations of this query without success. Non of my books show ...

  1. #1

    Default Multi table queries with agregates

    I have a master part list table and a in stock table with an entry for each
    part. I want a result set from the master table with a count for the parts
    in the stock table.
    I tried this query:

    select m.Part_no, m.Man, m.Model_Application, m.Years, m.Disp,
    m.description, m.catalog_comments, m.style, m.sugg_ret, (count(b.part_no))
    onhand
    from makemodel m, bin_list b
    where m.part_no = b.part_no
    group by m.part_no, b.part_no

    and I ger the error:

    When GROUP BY exists, every simple field in projectors must be in GROUP BY.

    I have tried several variations of this query without success. Non of my
    books show this done corectly and what is a projector?
    TIA for any help on this problem.
    Joe...


    JoeProgrammer Guest

  2. #2

    Default Re: Multi table queries with agregates

    Assuming that part_no is the primary key in MakeModel:

    SELECT m.Part_no, m.Man, m.Model_Application, m.Years, m.Disp,
    m.[description], m.catalog_comments, m.style, m.sugg_ret,
    COUNT(b.part_no) AS onhand
    FROM makemodel m, bin_list b
    WHERE m.part_no = b.part_no
    GROUP BY m.Part_no, m.Man, m.Model_Application, m.Years, m.Disp,
    m.[description], m.catalog_comments, m.style, m.sugg_ret

    Column names in the SELECT list of a GROUP BY query must either be in the
    GROUP BY list or must be enclosed in an aggregate function (eg. MIN() or
    MAX().

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  3. #3

    Default Re: Multi table queries with agregates

    Thanks David,
    Thanks so much for the quick response!
    This worked after removing the braces around description but returned only
    items where instock where not NIL. This helps but I want to show all part
    No's in a grid with count (onhand) even if there are none (0) on hand.
    Thanks again,
    Joe...


     


    JoeProgrammer Guest

  4. #4

    Default Re: Multi table queries with agregates

    David,
    My first reply didn't seem to go through.
    Thank you for the help. This worked but only gave a result set where the
    part was in both tables.
    I need to create a query that gives all rows of the master table
    (makemodel) and the count in the bin_list even if the count is (0)
    Thanks again for your help,
    Joe...

     


    JoeProgrammer Guest

  5. #5

    Default Re: Multi table queries with agregates

    Many Thanks, That works perfectly.
    I wish the SQL books I have were as informative.
    Thanks again,
    Joe...


    "David Portas" <org> wrote in message
    news:phx.gbl... 


    JoeProgrammer Guest

Similar Threads

  1. Multi-table search
    By Chris in forum MySQL
    Replies: 4
    Last Post: June 1st, 08:58 PM
  2. multi table select
    By Chuck Anderson in forum MySQL
    Replies: 3
    Last Post: February 10th, 06:39 AM
  3. SQL Pivot Table Queries
    By Tom in forum Dreamweaver AppDev
    Replies: 3
    Last Post: June 21st, 08:11 PM
  4. Replies: 0
    Last Post: July 23rd, 11:30 PM
  5. Multi-Table Forms - What is the best way?
    By SK in forum Microsoft Access
    Replies: 2
    Last Post: August 5th, 03:18 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139