Logic of "group by


If you use any aggregate function, and also wants to display another column along size it, you must use "GROUP by"
select char_length(city) as citynamesize,city from station group by city order by citynamesize DESC;

 
21 Marine On Saint Croix 
18 West Baden Springs 
17 Frankfort Heights 
3 Roy 
3 Lee




SELECT count(CustomerID), CustomerName,City FROM Customers where CustomerName Like '%Ho%' Group by City ;

 If you run below querry, it will give 7 in count column, because it's illogical, you can find the people/row, whos names contains "Ho", but against what, you cant display 7|Hosa ?

 SELECT count(CustomerID), CustomerName FROM Customers where CustomerName Like '%Ho%';

 So that's why it should be more meaningful, like display the names and count of people, whos name contains "Ho" per city,
So against each city, you will find count of people. whos name starts with "Ho" but again displaying names doesn't make sense, if you sane two person in Dlehi, with name "Hosa,Hola", what you will display in name coulum ?


count(CustomerID)CustomerNameCity
1Chop-suey ChineseBern
1Rancho grandeBuenos Aires
1Gourmet LanchonetesCampinas
1Princesa Isabel VinhossLisboa
2Consolidated HoldingsLondon
1Let's Stop N ShopSan Francisco

Comments

Post a Comment

Popular posts from this blog

Oracle User vs Oracle Schemas

Oracle schema = Oracle user + database objects owned by that user. When an Oracle user is created using the CREATE USER command, a schema gets created for the user by default with the same name as User There is a one-to-one correspondence between Oracle user name and Oracle schema name. While user = schema in most circumstances, that isn’t true all the time. CREATE SCHEMA does not actually create a schema. It only lets you create multiple tables/views and grant privileges on them in your own schema, in a single transaction. Example:  Here’s an example of using CREATE SCHEMA in the schema “hr” to create a table, create a view on that table, and give grant to another user on that view. SQL> CREATE SCHEMA AUTHORIZATION hr
Read more

Having and Where Clause

Where and Having both are used to apply filter Difference: You can not use Aggregate function in where clause, like select * from employee where SUM(salary) > 5000; but you can use Aggregate function in having clause. But having has to be used in conjunction with " GROUPED BY " clause. GROUP BY, makes the list of group, say if you have applied two " Grouped by" select city,gender   from employee   grouped by city, gender; in above case, you will have below type of  data Group-1 id  name      city      gender salary 2   Dharmu  BLR    M         4000 8   deepak    BLR   ML       5000 Group-2 id  name      city      gender salary 1   rashmi    BLR    F          10000 3   Sargun    BLR...
Read more