Is it possible to specify condition in Count()?
If you can’t just limit the query itself with a where clause, you can use the fact that the count aggregate only counts the non-null values: You can also use the sum aggregate in a similar way:
tsql
Can I use multiple “with”?
Try: And yes, you can reference common table expression inside common table expression definition. Even recursively. Which leads to some very neat tricks.
SQL: IF clause within WHERE clause
Use a CASE statementUPDATE: The previous syntax (as pointed out by a few people) doesn’t work. You can use CASE as follows: Or you can use an IF statement like @N. J. Reed points out.
Get current year in TSQL
Yes, it’s remarkably easy in fact:
Format number as percent in MS SQL Server
M.Ali’s answer could be modified as
How do I format a number with commas in T-SQL?
In SQL Server 2012 and higher, this will format a number with commas: You can also change 0 to the number of decimal places you want.
Equivalent of Oracle’s RowID in SQL Server
From the Oracle docs ROWID Pseudocolumn For each row in the database, the ROWID pseudocolumn returns the address of the row. Oracle Database rowid values contain information necessary to locate a row: The data object number of the object The data block in the datafile in which the row resides The position of the row … Read more
SQL “select where not in subquery” returns no results
Update: These articles in my blog describe the differences between the methods in more detail: NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: PostgreSQL NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: Oracle NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL There are three ways to do such a query: LEFT JOIN … Read more
How to format a numeric column as phone number in SQL
This should do it: Incorporated Kane’s suggestion, you can compute the phone number’s formatting at runtime. One possible approach would be to use scalar functions for this purpose (works in SQL Server):
TSQL PIVOT MULTIPLE COLUMNS
Since you want to pivot multiple columns of data, I would first suggest unpivoting the result, score and grade columns so you don’t have multiple columns but you will have multiple rows. Depending on your version of SQL Server you can use the UNPIVOT function or CROSS APPLY. The syntax to unpivot the data will be similar to: See SQL Fiddle … Read more