#1055 - Expression #19 of SELECT list is not in GROUP BY clause and contains nonaggregated column

Question

I have this error displaying in my sql query :

"Expression #19 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'auctionsystems.auction_watches.watchId' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_byDatabase select query failed."

I was trying to get the number of auctions a user is watching from the auction list, but when a user chose multiple auctions to watch the error displays. Can someone tell me what is wrong with the query:

SELECT auctions.auctionId, quantity, startPrice, reservePrice, startTime,
        endTime, itemName, itemBrand, itemDescription, items.image, auctions.views,
        item_categories.categoryName as subCategoryName, superCategoryName,
        item_categories.superCategoryId, item_categories.categoryId, users.username as sellerUsername,
        conditionName, countryName, auction_watches.watchId, COUNT(DISTINCT (bids.bidId)) AS numBids,
        MAX(bids.bidPrice) AS highestBid,
        case
            when MAX(bids.bidPrice)is not null THEN MAX(bids.bidPrice)
            else startPrice
        end as currentPrice,
        case
            when MAX(bids.bidPrice) > auctions.reservePrice AND auctions.endTime < now() then 1
            else 0
        end as sold


        FROM auctions
            LEFT OUTER JOIN bids ON bids.auctionId = auctions.auctionId
            JOIN auction_watches ON auction_watches.auctionId = auctions.auctionId
            JOIN items ON items.itemId = auctions.itemId
            JOIN users ON items.userId = users.userId
            JOIN item_categories ON items.categoryId = item_categories.categoryId
            JOIN super_item_categories ON  item_categories.superCategoryId = super_item_categories.superCategoryId
            JOIN item_conditions ON items.conditionId = item_conditions.conditionId
            JOIN countries ON users.countryId = countries.countryId

        WHERE auction_watches.watchId IN( 19706, 19707 )

        GROUP BY auctions.auctionId

        ORDER BY CASE WHEN auctions.endTime > now() THEN 0 ELSE 1 END ASC, auctions.endTime ASC

Answer 1

You need to add all other columns except aggregated columns in group by clause

SELECT auctions.auctionId, quantity, startPrice, reservePrice, startTime,
        endTime, itemName, itemBrand, itemDescription, items.image, auctions.views,
        item_categories.categoryName as subCategoryName, superCategoryName,
        item_categories.superCategoryId, item_categories.categoryId, users.username as sellerUsername,
        conditionName, countryName, auction_watches.watchId, COUNT(DISTINCT (bids.bidId)) AS numBids,
        MAX(bids.bidPrice) AS highestBid,
        case
            when MAX(bids.bidPrice)is not null THEN MAX(bids.bidPrice)
            else startPrice
        end as currentPrice,
        case
            when MAX(bids.bidPrice) > auctions.reservePrice AND auctions.endTime < now() then 1
            else 0
        end as sold


        FROM auctions
            LEFT OUTER JOIN bids ON bids.auctionId = auctions.auctionId
            JOIN auction_watches ON auction_watches.auctionId = auctions.auctionId
            JOIN items ON items.itemId = auctions.itemId
            JOIN users ON items.userId = users.userId
            JOIN item_categories ON items.categoryId = item_categories.categoryId
            JOIN super_item_categories ON  item_categories.superCategoryId = super_item_categories.superCategoryId
            JOIN item_conditions ON items.conditionId = item_conditions.conditionId
            JOIN countries ON users.countryId = countries.countryId

        WHERE auction_watches.watchId IN( 19706, 19707 )

        GROUP BY auctions.auctionId, quantity, startPrice, reservePrice, startTime,
        endTime, itemName, itemBrand, itemDescription, items.image, auctions.views,
        item_categories.categoryName as subCategoryName, superCategoryName,
        item_categories.superCategoryId, item_categories.categoryId, users.username as sellerUsername,
        conditionName, countryName, auction_watches.watchId

        ORDER BY CASE WHEN auctions.endTime > now() THEN 0 ELSE 1 END ASC, auctions.endTime ASC