Group by query in sql

Question

I have got 2 tables, Security and SecurityTransactions.

Security:

create table security(SecurityId int, SecurityName varchar(50));

insert into security values(1,'apple');

insert into security values(2,'google');

insert into security values(3,'ibm');

SecurityTable:

create table SecurityTransactions(SecurityId int, Buy_sell boolean, Quantity int);

insert into securitytransactions values ( 1 , false, 100 );

insert into securitytransactions values ( 1 , true, 20 );

insert into securitytransactions values ( 1 , false, 50 );

insert into securitytransactions values ( 2 , false, 120 );

I want to find out the security name and it's no of appearance in SecurityTransactions.

The answer is below:

SecurityName | Appearance
apple        | 3
google       | 1

I wrote the below sql query :

select S.SecurityName, count(t.securityID) as Appearance
from security S inner join securitytransactions t on S.SecurityId = t.SecurityId
group by t.SecurityId, S.SecurityName;

this query gave me the desired result, but it was still rejected by a person saying group by should have been s.securityName. why is it so ?

EDIT :

Which one do you this is correct and why ?

a. group by t.SecurityId, S.securityName

b. group by t.SecurityId

c. group by S.securityName

It sounds like you are asking about `GROUP BY`. MySQL's old default behavior is lenient and permits you to include columns in `SELECT` not present in `GROUP BY`, but most other RDBMS would reject that query. Additionally, the behavior is _off by default_ in MySQL 5.7 so it is not recommended to rely on it, and a bad habit to have when you switch to another RDMBS. http://stackoverflow.com/questions/38907729/mysql-5-7-only-full-group-by — Michael Berkowski, Jan 06 '17 at 19:17
Grumble. Question changed materially after I answered it. There's nothing wrong with the query as changed. The "person" is wrong. It's fine to group by columns that don't appear in the result set. — O. Jones, Jan 06 '17 at 19:25
O.Jones, Could you please elaborate on why the query I have written is right ? — PramTal, Jan 06 '17 at 19:26

Mureinik · Answer 1 · 2017-01-06T19:27:05.020

2

According to ANSI SQL, if you use a group by clause your select list may only contain items in the group by clause, single row transformations thereof, or aggregate expressions. MySQL is non-standard, and allows other columns too. In this case it happened to produce the right answer, as there's a 1:1 relationship between the SecuirtyId and SecurityName, but generally speaking, this is a bad practice that will make your code hard to understand at best, and unpredictable at worst.

EDIT:
To address the edited question - grouping by both SecuirtyId and SecurityName isn't technically wrong, it's just redundant. Since there's a 1:1 relationship between the two columns, adding the SecurityId column to the group by clause won't change the result, and will just confuse people reading the query.

edited Jan 06 '17 at 19:27

answered Jan 06 '17 at 19:19

Mureinik

297,002
52
306
350

Sorry, I have updated my original query with the group by clause now involving both fields, could you please suggest now? – PramTal Jan 06 '17 at 19:24
Mureinik, what if I group by with just SecurityId ? in that case, will it be right ? Also, do you mean I should just write group by SecurityName ? – PramTal Jan 06 '17 at 19:31
@PramTal `group by SecurityId;` would be (technically) wrong (although some mysql versions allow it); `group by SecurityId, SecurityName;` would technically work in your case (so isn't technically wrong) - but there is actually something to consider: the two version will behave differently if you can have two companies with the same name/different id. Anyway, currently it doesn't belong there logically: You want to group by the name. So group by the name. `group by t.SecurityId+1, t.SecurityId+3, S.SecurityName;` would technically work too, but you would ask yourself why I would write THAT. – Solarflare Jan 07 '17 at 02:20

Group by query in sql

1 Answers1