• Register
–1 vote
60 views

Problem :

I want to display the column B in my below mentioned SQL query, but when I try to add it to my query it gives me following error:

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

 

My code:

SELECT L, COUNT(M) as T1, M 
FROM T2 
WHERE ID=1 
GROUP BY L 
6 5 3
7,540 points

Please log in or register to answer this question.

1 Answer

0 votes

Solution :

This error is trying to tell you that SQL Server is unaware of which M to select from the group.

So either you want to select one specific value in that case you need to use the appropriate aggregate function. Or if you want to select every value as the new row.

Please consider following exaples:

Our Data:

ID  L   M

1   1  13

1   1  79

1   2  13

1   2  13

1   2  42

Our query:

SELECT L, COUNT(M) AS T1

FROM T2

GROUP BY L

will return:

L  T1

1  2

2  3

which is correct.

Now consider the following (incorrect) query, which will produce the following error:

SELECT L, COUNT(M) AS T1, M

FROM T2

GROUP BY L

And it is returned the data set illustrating the problem:

L T1  M

1  2   13? 79? Both 13 and 79 as separate rows? (13+79=92)? ...?

2  3   13? 42? ...?

But now the following two queries will make this clear and will not cause any error:

1.      Using an aggregate query

SELECT L, COUNT(M) AS T1, SUM(M) AS M

FROM T2

GROUP BY L

will return:

L  T1  M

1  2   92

2  3   68

2.      Adding the column to the GROUP BY list query

SELECT L, COUNT(M) AS T1, M

FROM T2

GROUP BY L, M

will return:

L  T1  M

1  1   13

1  1   79

2  2   13

2  1   42

I hope it clears your issues.

9 7 4
38,600 points

Related questions

0 votes
1 answer 104 views
104 views
Problem : I want to select a maximum avg for each cname in my table. SELECT cname, wmname, MAX(avg)  FROM makerar GROUP BY cname; But I will get the below error, ERROR:  column "makerar.wmname" must appear in the GROUP BY clause or be used in an   aggregate ... )  FROM makerar GROUP BY cname; How can I go about fixing above error? Note: This table is the VIEW created from the previous operation.
asked Jan 22, 2020 jwilliam 3.9k points
0 votes
1 answer 33 views
33 views
Problem : Currently I am using the MySQL 5.7.13 on the windows PC with the WAMP Server While executing below query I am facing following error SELECT * FROM `tbl_customer_pod_uploads` WHERE `load_id` = '78' AND `status` = ' ... column 'returntr_prod.tbl_customer_pod_uploads.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
asked Feb 18, 2020 mphil 2.3k points
0 votes
1 answer 34 views
34 views
Problem : I am currently using MySQL 5.7.13 on my windows PC with the WAMP Server I am facing following error while executing below the query. Error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ' ... functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by Does anybody have any solution for above error?
asked Nov 29, 2019 alecxe 7.5k points
0 votes
2 answers 187 views
187 views
Problem : I know that my query below causes the error - ORDER BY items must appear in the select list if SELECT DISTINCT is specified. SELECT DISTINCT mycity FROM Employees WHERE mycountry = N'USA' AND myregion = N'WA' ORDER BY mybirthdate; What is a reason for ... gives me some output, but not the error. Is it because SELECT DISTINCT mycity only gives the result set with cities and nothing else ?
asked Jan 17, 2020 jwilliam 3.9k points
0 votes
1 answer 195 views
195 views
Problem : My stored procedure giving me following error cannot perform an aggregate function on an expression containing an aggregate or a subquery.
asked Nov 15, 2019 peterlaw 6.9k points
0 votes
1 answer 9 views
1 vote
1 answer 27 views
27 views
Problem : I am very beginner to SQL Server. Currently I am trying to execute my SQL Server SUBSTRING query written by me but I am facing error. Please find below my query for your reference: select     substring(mytestcasename, 2, 5) val1,     substring(mytestcasename, ... 16, State 5, Line 1 Invalid length parameter passed to the LEFT or SUBSTRING function. Please guide me in fixing above error.
asked May 9, 2020 stewart 4k points
0 votes
1 answer 47 views
47 views
Problem : I have written two different types of sticky menus for two different pages. Following is the error Inside the anonymous function, use jquery to select the footer div. it has an id of 'footer'. Following is the code for both Menus. $(document).ready(function () { ... the bottom doesn't work because the second line of code var contentNav = $('.content-nav').offset().top; fires a error .
asked Nov 19, 2019 peterlaw 6.9k points
0 votes
1 answer 557 views
557 views
Problem : Recently I installed the python3 and also jupyter notebook using the pip3 on MacOs 10.9. But when I want to run my widget it gives me error that there is no javascript widget. I have the python3 and R kernels installed in my Jupyter-notebook. ... that in my mac I have the jupyter-nbextension command but the command: jupyter-nbextension enable --py widgetsnbextension Does not work for me.
asked Jan 27, 2020 jwilliam 3.9k points