You can use a CASE statement with an aggregate function. This is basically the same thing as a PIVOT function in some RDBMS: SELECT distributor_id, count(*) AS total, sum(case when level=”exec” then 1 else 0 end) AS ExecCount, sum(case when level=”personal” then 1 else 0 end) AS PersonalCount FROM yourtable GROUP BY distributor_id
First off, if you’re starting a new project, go with Entity Framework (“EF”) – it now generates much better SQL (more like Linq to SQL does) and is easier to maintain and more powerful than Linq to SQL (“L2S”). As of the release of .NET 4.0, I consider Linq to SQL to be an obsolete … Read more
select * from dbo.March2010 A where A.Date >= Convert(datetime, ‘2010-04-01’ ) In your query, 2010-4-01 is treated as a mathematical expression, so in essence it read select * from dbo.March2010 A where A.Date >= 2005; (2010 minus 4 minus 1 is 2005 Converting it to a proper datetime, and using single quotes will fix this … Read more
You can enter the following psql meta-command to get some details about a specified database, including its size: \l+ <database_name> And to get sizes of all databases (that you can connect to): \l+
You’re looking for a group by: select * from table group by field1 Which can occasionally be written with a distinct on statement: select distinct on field1 * from table On most platforms, however, neither of the above will work because the behavior on the other columns is unspecified. (The first works in MySQL, if … Read more
No CURSOR, WHILE loop, or User-Defined Function needed. Just need to be creative with FOR XML and PATH. [Note: This solution only works on SQL 2005 and later. Original question didn’t specify the version in use.] CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT) INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,’A’,4) INSERT INTO #YourTable ([ID],[Name],[Value]) … Read more
Postgres allows: UPDATE dummy SET customer=subquery.customer, address=subquery.address, partn=subquery.partn FROM (SELECT address_id, customer, address, partn FROM /* big hairy SQL */ …) AS subquery WHERE dummy.address_id=subquery.address_id; This syntax is not standard SQL, but it is much more convenient for this type of query than standard SQL. I believe Oracle (at least) accepts something similar.
Here is how it works: 1. Get XML element string with FOR XML Adding FOR XML PATH to the end of a query allows you to output the results of the query as XML elements, with the element name contained in the PATH argument. For example, if we were to run the following statement: SELECT … Read more
Part 1 – Joins and Unions This answer covers: Part 1 Joining two or more tables using an inner join (See the wikipedia entry for additional info) How to use a union query Left and Right Outer Joins (this stackOverflow answer is excellent to describe types of joins) Intersect queries (and how to reproduce them … Read more
There are several ways that you can transform data from multiple rows into columns. Using PIVOT In SQL Server you can use the PIVOT function to transform the data from rows to columns: select Firstname, Amount, PostalCode, LastName, AccountNumber from ( select value, columnname from yourtable ) d pivot ( max(value) for columnname in (Firstname, … Read more