sum of particular column getting error

Question

I have a query like this:

SELECT  DISTINCT  
    dbo.T_Order_Header.F_Exhibitor, 
    dbo.T_Order_Header.F_Exhibition, 
    dbo.T_Exhibition.F_Exhibition_Name,
    dbo.T_Exhibitor.F_Exhibitor_Name,
    dbo.T_Order_Detail.F_ItemCode,
    dbo.T_L2Category.F_L2Cat_Name, 
    SUM(dbo.T_Order_Detail.F_Qty-dbo.T_Order_Detail.F_CNQty) AS F_Qty,
    dbo.T_L1Category.F_L1Cat_Name,
    dbo.T_Order_Header.F_Stand,
    dbo.T_Category.F_Cat_name,
    dbo.T_ExStand.F_Bld_Code, 
    dbo.T_ExBuilding.F_Bld_name  
FROM dbo.T_Order_Header 
LEFT OUTER JOIN dbo.T_OrderAttachment 
    ON dbo.T_OrderAttachment.F_OrderNumber  = dbo.T_Order_Header.F_OrderNumber 
LEFT OUTER JOIN dbo.T_Order_Detail 
    ON dbo.T_Order_Detail.[Header_ID] = dbo.T_Order_Header.[ID]
LEFT OUTER JOIN dbo.T_L2Category 
    ON dbo.T_Order_Detail.F_ItemCode = dbo.T_L2Category.F_ItemCode
LEFT OUTER JOIN dbo.T_L1Category 
    ON dbo.T_L1Category.F_L1Cat_Code  = dbo.T_L2Category.F_L1Cat_Code
LEFT OUTER JOIN dbo.T_Category 
    ON dbo.T_Category.F_Cat_Code = dbo.T_L2Category.F_Cat_Code
LEFT OUTER JOIN dbo.T_ExStand  
    ON dbo.T_ExStand.F_Stand_Code = dbo.T_Order_Header.F_Stand
LEFT OUTER JOIN dbo.T_ExBuilding  
    ON dbo.T_ExStand.F_Bld_Code = dbo.T_ExBuilding.F_Bld_Code
LEFT OUTER JOIN dbo.T_Exhibition 
    ON dbo.T_Order_Header.F_Exhibition = dbo.T_Exhibition.F_Exhibition_Code
LEFT OUTER JOIN dbo.T_Exhibitor 
    ON dbo.T_Order_Header.F_Exhibitor = dbo.T_Exhibitor.F_Exhibitor_Code 
WHERE
    F_Stand IN(
        SELECT F_Stand_Code 
        FROM T_ExStand 
        WHERE
            F_Site_Code ='DWTC' 
            AND F_Bld_Code = 'HALL1-4 & CONCOURSE'
    )
    AND T_Order_Header.F_Exhibition = '12004'
    AND T_Order_Header.F_IsActive = 1 
    AND F_Exhibitor='2467'

I want to show sum of F_qty but I keep getting this error:

Column 'dbo.T_Order_Header.F_Exhibitor' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

if i add sum i am getting error like this:Column 'dbo.T_Order_Header.F_Exhibitor' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. — jas backer, Sep 14 '15 at 05:58
Post the query that contains `SUM`, with the error you are getting — Luthando Ntsekwa, Sep 14 '15 at 05:59
You don't even have a `SUM` in your `SELECT`. How are you getting that error? — Felix Pamittan, Sep 14 '15 at 06:00
Ask yourself, sum of numbers in what rows are you trying to compute? — Sergey Kalinichenko, Sep 14 '15 at 06:01
sir other wise..i dont want to take Itemcode distinct is it possible? — jas backer, Sep 14 '15 at 06:02
Instead of `DISTINCT` try using `GROUP BY` and include all the columns in the `SELECT`. — Felix Pamittan, Sep 14 '15 at 06:11

Shilpa Soni · Answer 1 · 2015-09-14T06:22:33.293

You need to group by with all the columns which you're selecting (other than the one which is in the aggregate function), something like below query:

SELECT  
dbo.T_Order_Header.F_Exhibitor, 
dbo.T_Order_Header.F_Exhibition, 
dbo.T_Exhibition.F_Exhibition_Name,
dbo.T_Exhibitor.F_Exhibitor_Name,
dbo.T_Order_Detail.F_ItemCode,
dbo.T_L2Category.F_L2Cat_Name, 
SUM(dbo.T_Order_Detail.F_Qty-dbo.T_Order_Detail.F_CNQty) AS F_Qty,
dbo.T_L1Category.F_L1Cat_Name,
dbo.T_Order_Header.F_Stand,
dbo.T_Category.F_Cat_name,
dbo.T_ExStand.F_Bld_Code, 
dbo.T_ExBuilding.F_Bld_name  
 FROM dbo.T_Order_Header 
LEFT OUTER JOIN dbo.T_OrderAttachment 
ON dbo.T_OrderAttachment.F_OrderNumber  = dbo.T_Order_Header.F_OrderNumber 
LEFT OUTER JOIN dbo.T_Order_Detail 
ON dbo.T_Order_Detail.[Header_ID] = dbo.T_Order_Header.[ID]
LEFT OUTER JOIN dbo.T_L2Category 
ON dbo.T_Order_Detail.F_ItemCode = dbo.T_L2Category.F_ItemCode
LEFT OUTER JOIN dbo.T_L1Category 
ON dbo.T_L1Category.F_L1Cat_Code  = dbo.T_L2Category.F_L1Cat_Code
LEFT OUTER JOIN dbo.T_Category 
ON dbo.T_Category.F_Cat_Code = dbo.T_L2Category.F_Cat_Code
LEFT OUTER JOIN dbo.T_ExStand  
ON dbo.T_ExStand.F_Stand_Code = dbo.T_Order_Header.F_Stand
LEFT OUTER JOIN dbo.T_ExBuilding  
ON dbo.T_ExStand.F_Bld_Code = dbo.T_ExBuilding.F_Bld_Code
LEFT OUTER JOIN dbo.T_Exhibition 
ON dbo.T_Order_Header.F_Exhibition = dbo.T_Exhibition.F_Exhibition_Code
LEFT OUTER JOIN dbo.T_Exhibitor 
ON dbo.T_Order_Header.F_Exhibitor = dbo.T_Exhibitor.F_Exhibitor_Code 
WHERE
F_Stand IN(
    SELECT F_Stand_Code 
    FROM T_ExStand 
      WHERE
          F_Site_Code ='DWTC' 
          AND F_Bld_Code = 'HALL1-4 & CONCOURSE'
   )
   AND T_Order_Header.F_Exhibition = '12004'
  AND T_Order_Header.F_IsActive = 1 
  AND F_Exhibitor='2467'
 group by  dbo.T_Order_Header.F_Exhibitor, 
 dbo.T_Order_Header.F_Exhibition, 
  dbo.T_Exhibition.F_Exhibition_Name,
 dbo.T_Exhibitor.F_Exhibitor_Name,
 dbo.T_Order_Detail.F_ItemCode,
 dbo.T_L2Category.F_L2Cat_Name, 
 dbo.T_L1Category.F_L1Cat_Name,
 dbo.T_Order_Header.F_Stand,
 dbo.T_Category.F_Cat_name,
 dbo.T_ExStand.F_Bld_Code, 
 dbo.T_ExBuilding.F_Bld_name

hi,,,this also not solving my problem.is there any way i can take only item code without distinct? — jas backer, Sep 14 '15 at 06:15
no mam..this also not solved my issue.actually if i add dbo.T_Order_Header.F_OrderNumber in my query my problem will solve.but i dont want to show this filed — jas backer, Sep 14 '15 at 06:27

Indra Prakash Tiwari · Answer 2 · 2015-09-14T06:34:45.170

Use below query for your requirement.

SELECT  DISTINCT  
    dbo.T_Order_Header.F_Exhibitor, 
    dbo.T_Order_Header.F_Exhibition, 
    dbo.T_Exhibition.F_Exhibition_Name,
    dbo.T_Exhibitor.F_Exhibitor_Name,
    dbo.T_Order.F_ItemCode,
    dbo.T_L2Category.F_L2Cat_Name, 
    T_Order.F_Qty F_Qty,
    dbo.T_L1Category.F_L1Cat_Name,
    dbo.T_Order_Header.F_Stand,
    dbo.T_Category.F_Cat_name,
    dbo.T_ExStand.F_Bld_Code, 
    dbo.T_ExBuilding.F_Bld_name  
FROM dbo.T_Order_Header 
LEFT OUTER JOIN dbo.T_OrderAttachment 
    ON dbo.T_OrderAttachment.F_OrderNumber  = dbo.T_Order_Header.F_OrderNumber 
LEFT OUTER JOIN 
(select  SUM(dbo.T_Order_Detail.F_Qty)-sum(dbo.T_Order_Detail.F_CNQty) AS F_Qty,
    [Header_ID],F_ItemCode from
     dbo.T_Order_Detail 
     group by 
     [Header_ID]) T_Order   ON dbo.T_Order.[Header_ID] = dbo.T_Order_Header.[ID]
LEFT OUTER JOIN dbo.T_L2Category 
    ON dbo.F_ItemCode.F_ItemCode = dbo.T_L2Category.F_ItemCode
LEFT OUTER JOIN dbo.T_L1Category 
    ON dbo.T_L1Category.F_L1Cat_Code  = dbo.T_L2Category.F_L1Cat_Code
LEFT OUTER JOIN dbo.T_Category 
    ON dbo.T_Category.F_Cat_Code = dbo.T_L2Category.F_Cat_Code
LEFT OUTER JOIN dbo.T_ExStand  
    ON dbo.T_ExStand.F_Stand_Code = dbo.T_Order_Header.F_Stand
LEFT OUTER JOIN dbo.T_ExBuilding  
    ON dbo.T_ExStand.F_Bld_Code = dbo.T_ExBuilding.F_Bld_Code
LEFT OUTER JOIN dbo.T_Exhibition 
    ON dbo.T_Order_Header.F_Exhibition = dbo.T_Exhibition.F_Exhibition_Code
LEFT OUTER JOIN dbo.T_Exhibitor 
    ON dbo.T_Order_Header.F_Exhibitor = dbo.T_Exhibitor.F_Exhibitor_Code 
WHERE
    F_Stand IN(
        SELECT F_Stand_Code 
        FROM T_ExStand 
        WHERE
            F_Site_Code ='DWTC' 
            AND F_Bld_Code = 'HALL1-4 & CONCOURSE'
    )
    AND T_Order_Header.F_Exhibition = '12004'
    AND T_Order_Header.F_IsActive = 1 
    AND F_Exhibitor='2467'

Msg 102, Level 15, State 1, Line 20 Incorrect syntax near '.'. Msg 156, Level 15, State 1, Line 45 Incorrect syntax near the keyword 'AND'. — jas backer, Sep 14 '15 at 06:19
@jasbacker let me know whether the column F_Stand from t_order_detail table? — Indra Prakash Tiwari, Sep 14 '15 at 06:26
@jasbacker actually i missed from statement before t_order_detail table. please use from before dbo.T_Order_Detail . — Indra Prakash Tiwari, Sep 14 '15 at 06:34

score 0 · Answer 3 · edited Sep 14 '15 at 07:18

0

You are using SQL group function. In this case you have to add GROUP BY clause at the end of the sql-statement. Please add all generic columns to GROUP BY clause except F_Qty. And remove DISTINCT from your select statement.

edited Sep 14 '15 at 07:18

Insane Skull

9,220
9
44
63

answered Sep 14 '15 at 06:21

pavel.konoplianik

1
1

sum of particular column getting error

3 Answers3