CODEWITHSUNDEEP
mysqloracle-database
User27854
User27854

Reputation: 884

Group by query in mysql and oracle

I have created a table and trying to learn how to use group by query but its giving weird output.

create table student(bid char(3), sname varchar(15), totfee float, feepaid float,
  feebal float, branch char(3), city char(4));


insert into student values(101,'student1',16000,8000,8000,'mat','bang');
insert into student values(102,'student2',17000,8000,9000,'mar','bang');
insert into student values(103,'student3',16000,9000,7000,'btm','bang');
insert into student values(104,'student4',12000,8000,4000,'amr','hyde');
insert into student values(105,'student5',14000,6000,8000,'mat','bang');
insert into student values(106,'student6',18000,8000,10000,'mar','bang');
insert into student values(107,'student7',16000,4000,12000,'btm','bang');
insert into student values(108,'student8',11000,2000,9000,'amr','bang');
insert into student values(109,'student9',13000,5000,8000,'btm','bang');
insert into student values(110,'student10',16000,3000,13000,'amr','hyde');

Now I am trying to sort the students based on branch. For that I tried the following query

In Oracle

select * from student group by branch;

output

Error- not a group by expression

In Mysql

output is : 

  +------+-----------+--------+---------+--------+--------+------+
  | bid  | sname     | totfee | feepaid | feebal | branch | city |
  +------+-----------+--------+---------+--------+--------+------+
  | 104  | student4  |  12000 |    8000 |   4000 | amr    | hyde |
  | 103  | student3  |  16000 |    9000 |   7000 | btm    | bang |
  | 113  | student13 |  36000 |   18000 |  18000 | mal    | bang |
  | 102  | student2  |  17000 |    8000 |   9000 | mar    | bang |
  | 101  | student1  |  16000 |    8000 |   8000 | mat    | bang |
  +------+-----------+--------+---------+--------+--------+------+

Actually I was expecting every it to group all the students based on branch.. So how do I do that? and what is actually the use of group by query? I even tried referring from website like w3school, and tpoint. But could not understand.

Upvotes: 0

Views: 193

Answers (6)

tooba jalali
tooba jalali

Reputation: 86

In Oracle you HAVE to name each column which is used in the group by expression in the select statement and if you use * in your select statement ,you have to name all of your columns in group by expression: select * from student s group by s.bid,s.sname,s.totfee,s.feepaid,s.feebal,s.branch,s.city which is not a real grouping and above query equals to select * from student,and no grouping is really done. you can have such queries in Oracle:

maximum totfee in each branch: 

select s.branch,max(s.totfee) from student s group by s.branch

in this query oracle groups all of data according to their branches . grouping based on branch means that you should have only 1 record for each branch like ‘amr’ in your output, normally you have 3 records for branch’amr’,thus you should do some works to delete extra rows and you should tell oracle which rows should be deleted and which ones should be remained and it’s the reason of existence of max(s.totfee),with max function ,you say oracle to keep just the row which has maximum totfee and delete other ones ,now you can have 1 output record for each branch .

Hint:If you wanna imply a condition you should use ‘having’ instead of where in above query.

number of branches which have more than 2 students:

select s.branch,count(*) from student  s group by s.branch having count(*)>2

Upvotes: 0

Hamidreza
Hamidreza

Reputation: 3128

In oracle you can use this query to get your final result:

with Tab AS (SELECT min(BID) AS ID FROM STUDENT GROUP BY BRANCH)
SELECT * FROM STUDENT WHERE BID IN (SELECT ID FROM Tab);

Upvotes: 0

tarzanbappa
tarzanbappa

Reputation: 4968

As you said that you want to Sort the students based on branch, Instead of using an "group by", You can use "order by".

select * from student order by branch;

Upvotes: 0

AHaberl
AHaberl

Reputation: 328

Group by only makes sense when used together with other aggregation functions like sum() or other. These aggregation functions combine the values of several rows of a column. For example sum() calculates the sum of all values of the specified column. In your example these columns could be totfee or feepaid. But this calculates the sum of all rows, if you want the sum for every branch separately calculated you can use the group by clause on the branch column. Now your database calculates the aggregation functions over the rows that have the same branch-value and the result contains the same number of rows as there are distinct values for branch in your table.

Upvotes: 3

Minciu
Minciu

Reputation: 96

'group by branch' will return all the different values for the 'branch' field. If you want to order the students based on the 'branch' field, you should use 'order by branch ASC' (or DESC).

Upvotes: 0

Jakub Matczak
Jakub Matczak

Reputation: 15686

You can't select fields that are not aggregated and are not in GROUP BY clause. Oracle is more strict about this rule in compare to MySQL. MySQL just takes first value, but Oracle throws an error.

Moreover GROUP BY is used to group records (aggregate). To sort results you should use ORDER BY.

Upvotes: 2

Related Questions