select list because it is not contained in either an aggregate function or the GROUP BY clause

Question

My query throws this error on execution - how to solve this?

Msg 8120, Level 16, State 1, Line 1
Column 'master_order.order_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Here is the query

SELECT 
   master_order.order_id, master_order.order_no, 
   master_order.program_no, master_order.package_type,
   article_production.article_code, article_production.weight,
   article_production.gsm, color.color_name, color.color_no,
   size.size_name, transaction_order.quantity, transaction_production.avrg, 
   transaction_production.total_weight, transaction_order.piece_carton, 
   transaction_order.no_of_carton, transaction_order.unit_name, 
   transaction_order.packs, transaction_order.kdnr,
   master_order.order_date, inlay.inlay_name, yarn.yarn_count,
   buyer.buyer_code, master_production.shipment_date,
   master_order.confirmation_date, master_order.comments, 
   master_production.carton_label, carton.carton_size, carton.carton_value,
   master_order.special_instruction, master_order.ean_code1,
   master_order.ean_code2, master_order.ean_code3, master_order.ean_code4, 
   article_production.article_name, transaction_order.piece_weight,
   article_production.machine_size, article_production.guage, 
   master_production.image_path, transaction_production.m3, 
   transaction_order.tabpage_no, transaction_order.serial_sort,
   master_order.total_tabpage,

When I add this error shown

  sum(distinct transaction_production.m3) as tm3

Continuing with existing code...

from 
    transaction_order 
LEFT JOIN 
    article_order on transaction_order.article_id = article_order.article_id
LEFT JOIN 
    size on transaction_order.size_id = size.size_id
LEFT JOIN 
    color on transaction_order.color_id = color.color_id
INNER JOIN 
    master_order ON transaction_order.order_id = master_order.order_id
LEFT JOIN 
    buyer ON master_order.buyer_id = buyer.buyer_id 
INNER JOIN 
    master_production ON master_order.order_id = master_production.order_id
LEFT JOIN 
    transaction_production ON transaction_order.trans_id = transaction_production.trans_id
LEFT JOIN 
    article_production on transaction_production.article_id = article_production.article_id
LEFT JOIN 
    inlay on master_production.inlay_id = inlay.inlay_id
LEFT JOIN 
    carton ON transaction_production.carton_id = carton.carton_id
LEFT JOIN 
    yarn ON transaction_production.yarn_id = yarn.yarn_id
WHERE
    master_order.program_no = '13-101117'

As soon as you used `sum(...)`, an aggregate function, you need to use a `GROUP BY` — James A Mohler, Dec 07 '13 at 07:16
i can display particular column where i error got so how to solve it — Salman Khan, Dec 07 '13 at 07:17
if i use group by then i will give all columns in group by its too long any short way to execute this query accurate — Salman Khan, Dec 07 '13 at 07:20
Create a CTE, or a function, or a view that does the grouping for `transaction_production` — James A Mohler, Dec 07 '13 at 07:24
A tip for next time: try to **reduce your problem** to the absolute minimum possible. 99% of all that query is totally irrelevant to the question, and it just makes it really really hard to spot what the problem really is. Try to show a **SIMPLE** example - not your full query with 12 joined table and dozens of columns being selected..... — marc_s, Dec 07 '13 at 08:08
possible duplicate of [Column "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"](http://stackoverflow.com/questions/18258704/column-invalid-in-the-select-list-because-it-is-not-contained-in-either-an-aggr) — Tanner, Apr 29 '15 at 13:44

score 0 · Answer 1 · answered Dec 07 '13 at 07:18

0

As soon as you used sum(...), an aggregate function, you need to use a GROUP BY

Furthermore, you have HTML in your SQL code.

... 
sum(distinct transaction_production.m3) as tm3 <p>when i add this error shown</p>
...

Is causing your problems

answered Dec 07 '13 at 07:18

James A Mohler

11,060
15
46
72

lol no i'm new in stackover flow so i can't know how to insert comments in code – Salman Khan Dec 07 '13 at 07:22
finally i use the group by but its too long to give all columns name if u can post any short way m very thank full of you if u can – Salman Khan Dec 07 '13 at 07:33
Create a CTE, or a function, or a view that does the grouping for `transaction_production` . On Stackoverflow, you have to try something first. http://meta.stackexchange.com/questions/156810/stack-overflow-question-checklist – James A Mohler Dec 07 '13 at 07:39

select list because it is not contained in either an aggregate function or the GROUP BY clause

1 Answers1