| Author |
Topic |
|
Doron100
Starting Member
5 Posts |
 Posted - 2009-01-03 : 06:18:48
|
| Hi everyone,I am a bit new to SQL and have a small question regarding the Trans command:For the table Money(name, money_recived, date)I have the following query, which calculates the average amount that a specific person received per day (taking in account that he can get several amounts in one day):SELECT SUM(day_total)/count(*) FROM ( SELECT SUM (money_recived) as day_total FROM Money Where name = 'John Smite' GROUP BY date) trans Without the trans command in the end, I get a syntax error.Can anyone tell me what is that trans command, what it doing and what it is used for? Why do I need it and when and where to use it?Thanks a lot.Doron |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-03 : 06:34:58
|
trans is actual table alias for derived table. When you form a derived table, you need to give a name for it which is called alias.b/w your query can be written without derived table also. just useSELECT SUM (money_recived)*1.0/ COUNT(DISTINCT date)FROM Money Where name = 'John Smite' |
 |
|
|
Doron100
Starting Member
5 Posts |
Posted - 2009-01-03 : 07:20:18
|
| Thanks a lot for the fast reply.So I understand that the ‘trans’ is just a given name and not a reserved command as I thought in the first place (…and that’s why I did not find it anywhere...)If so, why in the following example below you do not need to give an alias?The query supposes to find the last amount a person got before 1/1/2009:SELECT amount FROM MoneyWhere date = (SELECT MAX(date) FROM Money WHERE date < ‘2009-01-01’ AND name = ‘John Smith’) BTW, for your query, you are absolutely right. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-03 : 07:26:16
|
quote:
Originally posted by Doron100
Thanks a lot for the fast reply.So I understand that the ‘trans’ is just a given name and not a reserved command as I thought in the first place (…and that’s why I did not find it anywhere...)If so, why in the following example below you do not need to give an alias?The query supposes to find the last amount a person got before 1/1/2009:SELECT amount FROM MoneyWhere date = (SELECT MAX(date) FROM Money WHERE date < ‘2009-01-01’ AND name = ‘John Smith’) BTW, for your query, you are absolutely right.
in above query its not a derived table. its just a subquery which provides you with maximum date value from table.So it doesnt require a name to distinguish itself as its a single value rather than a resultset that is returned. |
|
|
|
Doron100
Starting Member
5 Posts |
Posted - 2009-01-03 : 07:40:14
|
| Thanks again. Still I am not sure I clearly understand when it is just a subquery and when it is a derived table (in which I have to use an alias).If I change the last example to:SELECT amount FROM MoneyWhere date IN (SELECT date FROM Money WHERE date < ‘2009-01-01’ AND name = ‘John Smith’)Now it returns multiple values (not a single value), but still it is not requiring an alias...(I do not know if this last example has any meaning, I just used it for demonstration...) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-03 : 07:52:16
|
quote:
Originally posted by Doron100
Thanks again. Still I am not sure I clearly understand when it is just a subquery and when it is a derived table (in which I have to use an alias).If I change the last example to:SELECT amount FROM MoneyWhere date IN (SELECT date FROM Money WHERE date < ‘2009-01-01’ AND name = ‘John Smith’)Now it returns multiple values (not a single value), but still it is not requiring an alias...(I do not know if this last example has any meaning, I just used it for demonstration...)
this is still a subquery and not a derived table.See below to understand differencehttp://www.sqlservercentral.com/Forums/Topic532691-338-1.aspx |
|
|
|
Doron100
Starting Member
5 Posts |
Posted - 2009-01-03 : 09:15:46
|
| Thanks. I looked at that forum and I must say I'm still confused....Is it related to the fact that the subquery (at least in my examples) is in the FROM section and not in the WHERE section? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-03 : 09:23:56
|
| just keep in mind that derived tables are like subqueries which are used mostly in joins which returns a resultset which is used to join with rest of query with help or 1 or more fields in it.A subquery on other hand will provide you with resultset which is used to correlate with main query with help of field |
|
|
|
Doron100
Starting Member
5 Posts |
Posted - 2009-01-03 : 10:31:08
|
| Now it is much more clear.Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-03 : 10:33:32
|
welcome  |
|
|
|
|
Question regarding derived table