SQL Server : SUM() of multiple rows including where clauses

This will bring back totals per property and type SELECT PropertyID, TYPE, SUM(Amount) FROM yourTable GROUP BY PropertyID, TYPE This will bring back only active values SELECT PropertyID, TYPE, SUM(Amount) FROM yourTable WHERE EndDate IS NULL GROUP BY PropertyID, TYPE and this will bring back totals for properties SELECT PropertyID, SUM(Amount) FROM yourTable WHERE EndDate … Read more

Count the Null columns in a row in SQL

This method assigns a 1 or 0 for null columns, and adds them all together. Hopefully you don’t have too many nullable columns to add up here… SELECT ((CASE WHEN col1 IS NULL THEN 1 ELSE 0 END) + (CASE WHEN col2 IS NULL THEN 1 ELSE 0 END) + (CASE WHEN col3 IS NULL … Read more

Join and Include in Entity Framework

Well, the Include contradicts the where. Include says, “Load all tags.” The where says, “Load some tags.” When there is a contradiction between the query and Include, the query will always win. To return all tags from any item with at least one tag == text: var items = from i in db.Items.Include(“Tags”) where i.Tags.Any(t … Read more

Getting the Last Insert ID with SQLite.NET in C#

Using C# (.net 4.0) with SQLite, the SQLiteConnection class has a property LastInsertRowId that equals the Primary Integer Key of the most recently inserted (or updated) element. The rowID is returned if the table doesn’t have a primary integer key (in this case the rowID is column is automatically created). See https://www.sqlite.org/c3ref/last_insert_rowid.html for more. As … Read more