Select two columns from two tables doesn't work

Question

I'm trying to get login and count that are in different tables but it doesn't work

Here is what I'm doing:

SELECT login AS "Login", COUNT(posted) AS "Nombre" FROM members, wallmsg;

I get the following error:

ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'sc_social.members.login'; this is incompatible with sql_mode=only_full_group_by

Is there another way to do this?

You should `group by login`... I think, and also provide the join between the 2 tables. — sanastasiadis, Jun 28 '18 at 07:31
share data set of two tables and expected result and your try that would be helpful — Zaynul Abadin Tuhin, Jun 28 '18 at 07:40

score 0 · Answer 1 · answered Jun 28 '18 at 07:35

To fetch data from two tables in SQL, you first need to add join between the tables. Whenever you uses any aggregate functions like "Count", you have to use "GroupBy" clause. SELECT m.login,COUNT(w.posted) FROM members m inner join wallmsg w on m.SomeId =w.SomeId group by m.login

score 0 · Answer 2 · answered Jun 28 '18 at 07:37

0

You have set sql mode on ONLY_FULL_GROUP_BY, you should change it by:

SET sql_mode = ''

and see this answer for detailed explanation

answered Jun 28 '18 at 07:37

godot

3,422
6
25
42

score 0 · Answer 3 · edited Jun 28 '18 at 07:52

0

try something like this:

SELECT
  Orders.OrderID,
  Customers.CustomerName,
  Orders.OrderDate
FROM Orders
  INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

edited Jun 28 '18 at 07:52

godot

3,422
6
25
42

answered Jun 28 '18 at 07:41

sandip bharadva

629
1
6
19

Select two columns from two tables doesn't work

3 Answers3