Show me something new in SQL..
Ok here are 2 new things that I have used only a few times in my programs. But
they maybe useful to you,so I shall talk about them. There are 2 keywords called
GROUP BY
and HAVING
.
Both these are used in conjunction with the aggregate statements like SUM
, AVG
, etc..
For all the examples in this article we would be using a sample database table
which is shown below
Table Name : companies
name | profit |
Sega
|
25000
|
Microsoft
|
50000
|
|
|
So what is GROUP BY? When do I use it?
The GROUP BY
keywords have been added to SQL
because aggregate functions (like SUM
) return the
aggregate of all column values every time they are called. Without the GROUP
BY
functionality, finding the sum for each individual group of column
values was not possible.
SELECT name, SUM(profit) FROM companies
Returns a recordset with 3 records. Each record has 2 values. The first record
would have the value 'Sega' and '85000'. The second record would have the values
' Microsoft' and '85000'. And the third record would have the values 'Sega' and
'85000'.
Thus it is clear that this is not what was required. There is no sense in getting
the sum of all the profits of all the companies along with each company name.
What would be acceptable is the sum of all the profits of the respective companies
along with that company's name. Read the next statement..SELECT name, SUM(profit) FROM companies GROUP BY name
Returns a recordset with 2 records. Each record has 2 values. The first record
would have the value 'Sega' and '35000'. The second record would have the values
'Microsoft' and '50000'.
And what is the HAVING keyword?
The HAVING
keyword has been added to SQL because
a WHERE
keyword can not be used against aggregate
functions (like SUM
). Without the HAVING
keyword it would not be possible to test for function result conditions.
SELECT name, SUM(profit) FROM companies GROUP BY name HAVING
SUM(profit)>40000
Returns a recordset with 1 record. This record would have 2 values, namely
'Microsoft' and '50000'. Since the sum of the profits of the company by the name
'Sega' is only 35000 (which is lesser than 40000 as required in the Query)
SELECT Company "Firm", Amount "Profit" FROM Sales
Alias - Returns the 2 cols with the heading as "Firm" and "Profit" instead of
"Company" & "Amount"
Do I have to work with the same column names that exist in the database
tables, within my program?
Yes and No. As far as the Query is concerned, you have to include the column
names that exist in the Database table in your Query. But you can also include
an alias with which you can carry on further work with the returned results
by the Database. Let me show you an example of using an aliasSELECT name "firm", profit "riches" FROM companies
Would return a recordset consisting of 3 records each with 2 values. Basically
all the 3 records from the sample database would be returned , but the column
names would be changed to those that were mentioned in the SQL statement. Thus
name would be changed to firm. And profit would change to
riches.
Comments