CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-by
user2730732
user2730732

Reputation: 1

Beginner at sql, need aid with using GROUP BY

This is the error I'm receiving: invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

When I program in 

SELECT prod.LineNumber ,
    "Pounds Made" = SUM(prod.PoundsMade) ,
    "Down Time" = SUM(prodDown.DownTimeHrs + DownTimeMins / 60) ,
    prodDown.LineNumber ,
    availHrs.LineNumber ,
    "Available Hours" = SUM(availHrs.AvailableHRS)
FROM   rpt_Line_Shift_Prod AS prod
    INNER JOIN rpt_Line_Shift_ProdDownTime AS prodDown
    INNER JOIN rpt_LINE_Shift_AvailableHrs AS availHrs ON prodDown.LineNumber = availHrs.LineNumber ON prodDown.LineNumber = prod.LineNumber
--ON availHrs.LineNumber = prod.LineNumber
GROUP BY prod.LineNumber

I'm using three tables here, all that I have renamed prodDown, prod, and availHrs.

THANK YOU!

Upvotes: 0

Views: 74

Answers (4)

Möoz
Möoz

Reputation: 863

When you use a SUM() or any other aggregate function on a column, you need to make sure that any other columns in your select list are also grouped. This will make sure that you do not get any duplicates. So as the other answers suggest, you need to make sure that you include your other columns in your GROUP BY clause.

Upvotes: 0

Hart CO
Hart CO

Reputation: 34784

SELECT  prod.LineNumber
      ,"Pounds Made" = SUM(prod.PoundsMade)
      ,"Down Time" = SUM(prodDown.DownTimeHrs + DownTimeMins/60)
      , prodDown.LineNumber
      , availHrs.LineNumber
      ,"Available Hours" = SUM(availHrs.AvailableHRS)     
FROM rpt_Line_Shift_Prod AS prod
INNER JOIN rpt_Line_Shift_ProdDownTime AS prodDown
    ON prodDown.LineNumber = prod.LineNumber
INNER JOIN rpt_LINE_Shift_AvailableHrs AS availHrs
    ON prodDown.LineNumber = availHrs.LineNumber
GROUP BY prod.LineNumber,prodDown.LineNumber, availHrs.LineNumber

Upvotes: 2

danisius
danisius

Reputation: 597

You group only by prod.LineNumber while in your select you also have prodDown.LineNumber, availHrs.LineNumber Add them in the group clause

GROUP by prod.LineNumber,prodDown.LineNumber, availHrs.LineNumber

Upvotes: 0

FrankPl
FrankPl

Reputation: 13315

Put prodDown.LineNumber``andavailHrs.LineNumber` into the GROUP BY clause as well:

GROUP BY prod.LineNumber,prodDown.LineNumber, availHrs.LineNumber

All columns in the select list that have no aggregation like sum need to be in the GROUP BY clause.

Upvotes: 0

Related Questions