-1

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

I am using Microsoft SQL Server. How do I solve this?

My syntax query is

SELECT 
[NoodleID] = REPLACE(Query.NoodleID, 'NO', 'Noodle'),
NoodleName,
NoodlePrice = 'Rp' + CAST(NoodlePrice AS VARCHAR)
FROM Noodle Query,
(
    SELECT 
    n.NoodleID,
    [Noodle Avg] = AVG(n.NoodlePrice)
    FROM Noodle n
    JOIN SalesTransactionDetail std ON std.NoodleID = n.NoodleID
    JOIN SalesTransaction st ON st.SalesTransactionID = std.SalesTransactionID
    WHERE DATENAME(MONTH, TransactionDate) = 'Wednesday'
) AS SubQuery
WHERE Query.NoodleID = SubQuery.NoodleID
GROUP BY NoodleName,NoodlePrice
Thom A
  • 88,727
  • 11
  • 45
  • 75
blue
  • 1
  • A `GROUP BY` needs to be in the query you are aggregating in; your aggregates are in the sub query and the `GROUP BY` in the outer query. – Thom A Jun 13 '21 at 14:06
  • 1
    `DATENAME(MONTH, TransactionDate) = 'Wednesday'` is not likely to ever be true... And you should never use `varchar` without a length – Charlieface Jun 13 '21 at 15:36
  • 1
    Have you not heard of the "Month" Wednesday, @Charlieface ? It's the month after Tuesday and before Thursday, and there's 4 or 5 of them a "year". It's a bit of an odd one though as most calenders use a 12 month calendar. ;) – Thom A Jun 13 '21 at 17:41
  • Worse than using [old-style joins](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins), you use both old and new styles. The habits you learn now will set the direction of your coding for years - learn GOOD habits. – SMor Jun 13 '21 at 20:03

1 Answers1

0

No need for a subquery here. Just something like:

SELECT NoodleID = REPLACE(n.NoodleID, 'NO', 'Noodle'),
       n.NoodleName,
       NoodlePrice = concat('Rp',AVG(n.NoodlePrice))
FROM Noodle n
JOIN SalesTransactionDetail std 
  ON std.NoodleID = n.NoodleID
JOIN SalesTransaction st 
  ON st.SalesTransactionID = std.SalesTransactionID
WHERE DATENAME(MONTH, TransactionDate) = 'Wednesday'
GROUP BY n.NoodleID, n.NoodleName
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Side note, I'd worry an it the currency formatting in the presentation layer, not the SQL. Otherwise a value like `'RP9'` is **greater than** a value like `'RP220.10'`. – Thom A Jun 13 '21 at 14:31