-1

I am writing a simple program using a database I've created but I have run into an issue, I am trying to determine the highest occurring referrer ID while also displaying the customer information

SELECT TOP (3) referrer_id,
    COUNT(referrer_id) AS value_occurance,
    customer_id, 
    CONCAT(first_name, ' ', last_name) AS full_name
FROM customer
GROUP BY referrer_id
ORDER BY value_occurance DESC

This is my query.

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

This is the error I am receiving.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Mitchell Downey
  • 11
  • 1
  • 1
  • 1
  • 1
    Add `customer_id, CONCAT(first_name, ' ', last_name)` to the GROUP BY, and see if you get what you want. – jarlh May 23 '21 at 20:43
  • Either add `customer.customer_id` into `GROUP BY`, or compute an aggregate function from it, say, `MAX(customer.customer_id)`; same for `CONCAT(first_name, ' ', last_name)` – Dmitry Bychenko May 23 '21 at 20:44
  • 1
    Based on the syntax, I added the SQL Server tag. It is quite clear why your code doesn't work -- is there something about the error message you don't understand? What is unclear is what you want to accomplish. Sample data, desired results, and a clear explanation of the logic would all be helpful. – Gordon Linoff May 23 '21 at 20:53
  • 5
    Does this answer your question? [Column "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"](https://stackoverflow.com/questions/18258704/column-invalid-in-the-select-list-because-it-is-not-contained-in-either-an-aggr) – Timothy G. May 23 '21 at 21:03

2 Answers2

1

One way to fix this is to add customer_id into your GROUP BY, So GROUP BY referrer_id, customer_id

kabax
  • 143
  • 1
  • 12
  • 1
    Not sure why OP hasnt marked this answer correct. I just upvoted you. helped me fix my code. thanks –  May 12 '22 at 00:30
0

Since you use GROUPBY, you need the column to either be one of the columns you are grouping by, or the columns you are aggregating by. Otherwise, it is invalid to select this column.

To fix, either add this column to the GROUPBY, or use an aggregation function using the customer column

TMmn
  • 44
  • 4