Error on Group by method

Question

I wrote a query to combine records in multiple tables. Tables named by Purchase Order, Purchase Order Item

   [ Note: The column names are not original names, it just for a model data]

In purchase order table have the order details like this,

id    date     vendorid   totalitems  totalqty   grossamnt   netamnt  taxamt
----------------------------------------------------------------------------
1   03/10/17     00001      2           6           12000     13000    1000

Purchase Order Item table have the order details like this,

poid  id  productcode  qty   rate  tax(%) taxamnt  total
--------------------------------------------------------
1      1     12001      3    6000   2.5    500     6500
2      1     12000      3    6000   2.5    500     6500

My Query is,

 select po.POID,po.SupplierId,po.TotalItems from 
  PurchaseOrder po, PurchaseOrderItem poi where po.POID=poi.POID group by 
  po.POID, po.SupplierId,po.TotalItems

Query returns,

 id  vendorid  totalitems
--------------------------
 1     00001      2
 1     00001      2

Expected Output is,

id vendorid totalitems
------------------------
1    00001    2

You are using some very outdated joining methods there, would advise against it. Also, you are joining, but never using the joined table, why is that? Remove the join and see what is going on. — Keith, Oct 03 '17 at 07:56

Keith · Accepted Answer · 2017-10-03T08:22:22.047

0

You are using an outdated join method, have a read here:

ANSI vs. non-ANSI SQL JOIN syntax

You are also joining to another table, but never use it:

select po.POID,po.SupplierId,po.TotalItems 
from PurchaseOrder po, PurchaseOrderItem poi 
where po.POID=poi.POID 
group by po.POID, po.SupplierId,po.TotalItems

Can just be:

select po.POID,po.SupplierId,po.TotalItems 
from PurchaseOrder po
group by po.POID, po.SupplierId,po.TotalItem

OR

select DISTINCT 
       po.POID,
       po.SupplierId,
       po.TotalItems 
from   PurchaseOrder po

edited Oct 03 '17 at 08:22

answered Oct 03 '17 at 08:02

Keith

1,008
10
19

Why even Group By clause, when there is no Aggregate function? – Ankit Bajpai Oct 03 '17 at 08:08
So it groups by what you have asked it to, without it the results would be different. Admittedly this could be replaced with DISTINCT - Updated my answer to include that. – Keith Oct 03 '17 at 08:21

Error on Group by method

1 Answers1