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. stackoverflow.com/questions/38907729/… — Michael Berkowski
– Michael Berkowski, Commented Jan 6, 2017 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
– O. Jones, Commented Jan 6, 2017 at 19:25
O.Jones, Could you please elaborate on why the query I have written is right ? — PramTal
– PramTal, Commented Jan 6, 2017 at 19:26

Mureinik · Accepted Answer · 2017-01-06 19:27:05Z

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 6, 2017 at 19:27

answered Jan 6, 2017 at 19:19

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

PramTal Over a year ago

Sorry, I have updated my original query with the group by clause now involving both fields, could you please suggest now?

PramTal Over a year ago

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 ?

Solarflare Over a year ago

@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.

Collectives™ on Stack Overflow

Group by query in sql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest