2

I have finally succeeded in summing the results of two sql sum queries. One small step for this guy:)

My question relates to the last character in the code (Z):

SELECT SUM(hr)
FROM   (SELECT SUM(amount) AS hr
        FROM   Try_again.dbo.tuesday_practice_database
        WHERE  account_name LIKE 'concessions'
        UNION
        SELECT SUM(amount) AS hr
        FROM   Try_again.dbo.tuesday_practice_database
        WHERE  account_name LIKE 'salaries')Z

The code won't work unless there is something following the closing parenthesis).

I'm just curious why that is and why does there need to be a character following the ).

It seems that it doesn't matter what the character is, the code will work. But the code breaks if I leave it blank.

Thank you

Md. Suman Kabir
  • 5,243
  • 5
  • 25
  • 43
AdMac
  • 117
  • 1
  • 12
  • 4
    It's the alias of the subquery. Basically, the result set needs to be identified, so needs an identifier: an alias. – HoneyBadger Oct 18 '17 at 13:33
  • Possible duplicate of [subquery in FROM must have an alias](https://stackoverflow.com/questions/14767209/subquery-in-from-must-have-an-alias) – shA.t Oct 18 '17 at 13:44
  • 2
    BTW There are a few issues there. Why are you using `LIKE` without a wildcard? That is clearer expressed as `=`. Why is this two queries at all? You could just do `where account_name in ('concessions', 'salaries')` - you want `UNION ALL` not `UNION` – Martin Smith Oct 18 '17 at 13:44

2 Answers2

3
SELECT sum(hr) 
FROM
(
   Select sum(amount) as hr 
   from Try_again.dbo.tuesday_practice_database 
   where account_name like 'concessions'
   union 
   Select sum(amount) as hr 
   from Try_again.dbo.tuesday_practice_database 
   where account_name like 'salaries'
) z

To understand better, look at the above version of your query. It's the same code, just reformatted to help illustrate what is happening. If you notice, the parent FROM clause retrieves data from a sub-query. In this context, SQL requires the subquery to have a name of some kind, and so the z is added as an alias to meet that requirement. You could put anything you wanted there, but since the name doesn't matter to us a single-letter placeholder is fine.

Just like the following query :

select * from table1 as z

By the way, you didn't use wildcard in your LIKE clause! I think you should re-write the query like below :

select sum(hr) from 
(
   Select sum(amount) as hr from Try_again.dbo.tuesday_practice_database 
   where account_name like '%concessions&'
   union all
   Select sum(amount) as hr from Try_again.dbo.tuesday_practice_database 
   where account_name like '%salaries%'
) AS z

If you don't want to use wildcard, then you should avoid using LIKE Use IN instead and re-write to this simple one :

Select sum(amount) from Try_again.dbo.tuesday_practice_database 
where account_name in('concessions', 'salaries')
Md. Suman Kabir
  • 5,243
  • 5
  • 25
  • 43
  • 1
    @jarlh If you want to improve the query, get rid of the UNION entirely and just do an `IN('concessions', 'salaries')`. But this isn't about improving the query. It's about explaining the alias. – Joel Coehoorn Oct 18 '17 at 14:04
  • 1
    The where condition can't prevent same sum from first and second select... UNION ALL is necessary. – jarlh Oct 18 '17 at 14:05
  • Oh, and leading wildcards (ie: `LIKE ('%concessions')` are almost always a bad idea. They remove any possibility of index use on that field and are very slow to evaluate. If he's getting the data he wants from `LIKE 'concessions'`, he can change to `= 'concessions'`, but adding the wildcard will make things worse, rather than better. – Joel Coehoorn Oct 18 '17 at 14:05
  • What's the expected result if an account_name 'concessions and salaries' would exist? – jarlh Oct 18 '17 at 14:06
  • @jarlh He was doing an exact match (no placeholders in the LIKE condition), so it wouldn't count with the original, either. – Joel Coehoorn Oct 18 '17 at 14:07
  • I think we could stop discussing on improving the query, he was just need of getting clarification why he was getting error on removing the ending `Z` in his query and i believe he got his answer now :) – Md. Suman Kabir Oct 18 '17 at 14:11
  • 1
    Just one more note on coding style, with `select` and `from` on the same line. Most conventions want all of the top-level sql keywords -- `WITH`, `SELECT`, `FROM`, `WHERE`, `ORDER BY`, `GROUP BY`, and (arguably) `HAVING` -- to each begin their own line. – Joel Coehoorn Oct 18 '17 at 14:30
2

Just do this:

SELECT SUM(amount) as hr
FROM Try_again.dbo.tuesday_practice_database
WHERE account_name IN ('concessions', 'salaries')

The existing code has a bug, where it will consolidate your two SUM()s if they are both the same amount. You could also fix this with UNION ALL instead of just UNION, but since the LIKE conditions don't have any placeholders like % or _ we can do better and simplify this whole thing down to just one IN() condition, with no need for any subqueries.

Now, if you wanted to allow more variance in your matches ( ie LIKE '%concessions%' and LIKE '%salaries%'), then the UNION ALL is more helpful... just be warned that leading % placeholders in a LIKE condition are very bad for performance, and should be avoided when possible. Often this means changing the schema in some way, such as adding a table named something like AccountCategories that group each account into a specific category that you can target exactly in your query.

But all of that side-steps the actual question: what is that z character?

In this case, it's an alias. You're using a subquery to union the two smaller queries together. In certain contexts, the SQL language requires subqueries to have a name. This includes using the subquery (derived table) as the target of a FROM, JOIN, or APPLY expression. You can use any name you want — it doesn't matter to the functioning of the query, since it's never referenced again — and so a simple single-letter placeholder, like z, is good enough.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794