I need to add additional columns to my application that are not part of GROUPBY statement

Question

I need some help with my SQL command. I have a VB.net application that uses few sql tables to get the final result. I need to add few additional columns from TableC into my application, but these columns are not part of GROUPBY function.

I tried to add simple select function to the code, but I always get the same error:

$"
SET NOCOUNT ON; 

SELECT CONVERT(VARCHAR(10), r.PossibleDate, 120) as [Date], 
**SELECT r.GStart as GStart,**
SUM(ISNULL(r.ElecCheckIn, 0)) as CheckIn, 
SUM(ISNULL(r.ElecCheckOut, 0)) as CheckOut, 
SUM(ISNULL(r.JPAmount, 0)) as AttAmountJP, 
SUM(ISNULL(r.MeteredAttAmountCC, 0)) as AttAmountCC, 
SUM(ISNULL(r.MeteredMachAmount, 0)) as MachAmount, 
SUM(ISNULL(r.MeteredAttAmount, 0)) as AttAmount, 
SUM(ISNULL(r.ElecCheckIn, 0) - ISNULL(r.ElecCheckOut, 0) - ISNULL(r.JPAmount, 0) - ISNULL(r.MeteredAttAmountCC, 0) - ISNULL(r.MeteredMachAmount, 0) - ISNULL(r.MeteredAttAmount, 0)) as NetWin
FROM dbo.CDS_TableA sm (NOLOCK) 
INNER JOIN dbo.bb_tableB st (NOLOCK) 
ON sm.TableB_Id=st.SlotB_Id 
AND sm.TableBRevision=st.TabelBRevision 
INNER JOIN dbo.TableC r (NOLOCK) 
ON sm.TableA_ID=r.TableA_ID 
AND r.PossibleDate BETWEEN '{dtStart.Value.ToString("yyyy-MM-dd")} 00:00:00' AND '{dtEnd.Value.ToString("yyyy-MM-dd")} 23:59:59'
AND r.Period_ID=4 
INNER JOIN dbo.BB_TableD rh (NOLOCK) 
ON sm.TableA_ID=rh.TableA_ID 
AND r.PossibleDate=rh.PossibleDate 
AND sm.Revision=rh.Revision 
WHERE sm.OnFloorFlag = 1 
AND sm.Calc_ID NOT IN (2,5) 
GROUP BY r.PossibleDate 
ORDER BY r.PossibleDate;

I always get an error that GStart is not contained in either an aggregate function or the GROUP BY clause.

score 0 · Accepted Answer · answered Jul 23 '19 at 19:17

Simply JOIN the aggregate level to unit level which can be facilitated with a CTE. Run this entire statement below including WITH clause.

WITH agg AS (
    SELECT CONVERT(VARCHAR(10), r.PossibleDate, 120) as [Date], 
           SUM(ISNULL(r.ElecCheckIn, 0)) as CheckIn, 
           SUM(ISNULL(r.ElecCheckOut, 0)) as CheckOut, 
           SUM(ISNULL(r.JPAmount, 0)) as AttAmountJP, 
           SUM(ISNULL(r.MeteredAttAmountCC, 0)) as AttAmountCC, 
           SUM(ISNULL(r.MeteredMachAmount, 0)) as MachAmount, 
           SUM(ISNULL(r.MeteredAttAmount, 0)) as AttAmount, 
           SUM(ISNULL(r.ElecCheckIn, 0) -
               ISNULL(r.ElecCheckOut, 0) - 
               ISNULL(r.JPAmount, 0) - 
               ISNULL(r.MeteredAttAmountCC, 0) - 
               ISNULL(r.MeteredMachAmount, 0) - 
               ISNULL(r.MeteredAttAmount, 0)) as NetWin
   FROM dbo.CDS_TableA sm (NOLOCK) 
   INNER JOIN dbo.bb_tableB st (NOLOCK) 
           ON sm.TableB_Id =s t.SlotB_Id 
          AND sm.TableBRevision=st.TabelBRevision 
   INNER JOIN dbo.TableC r (NOLOCK) 
           ON sm.TableA_ID= r.TableA_ID 
          AND r.PossibleDate BETWEEN '{dtStart.Value.ToString("yyyy-MM-dd")} 00:00:00' 
                                 AND '{dtEnd.Value.ToString("yyyy-MM-dd")} 23:59:59'
          AND r.Period_ID=4 
   INNER JOIN dbo.BB_TableD rh (NOLOCK) 
           ON sm.TableA_ID=rh.TableA_ID 
          AND r.PossibleDate=rh.PossibleDate 
          AND sm.Revision=rh.Revision 
        WHERE sm.OnFloorFlag = 1 
          AND sm.Calc_ID NOT IN (2,5) 
     GROUP BY r.PossibleDate 
)

SELECT r.GStart as GStart, agg.*    --- ADD OTHER r FIELDS
FROM dbo.TableC r
INNER JOIN agg ON CONVERT(VARCHAR(10), r.PossibleDate, 120) = agg.[Date]
ORDER BY r.PossibleDate

Aside: While I know nothing of vb.net, I do know running SQL at application layer and your concatenation of dates above should be parameterized values which is a programming industry best practice. See How do I create a parameterized SQL query? Why Should I? Also, use NOLOCK with caution.

I need to add additional columns to my application that are not part of GROUPBY statement

1 Answers1