StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>There isn't direct support for <code>COUNT(DISTINCT {x}))</code>, but you can simulate it from an <code>IGrouping<,></code> (i.e. what <code>group by</code> returns); I'm afraid I only "do" C#, so you'll have to translate to VB...</p> <pre><code> select new { Foo= grp.Key, Bar= grp.Select(x => x.SomeField).Distinct().Count() }; </code></pre> <p>Here's a Northwind example:</p> <pre><code> using(var ctx = new DataClasses1DataContext()) { ctx.Log = Console.Out; // log TSQL to console var qry = from cust in ctx.Customers where cust.CustomerID != "" group cust by cust.Country into grp select new { Country = grp.Key, Count = grp.Select(x => x.City).Distinct().Count() }; foreach(var row in qry.OrderBy(x=>x.Country)) { Console.WriteLine("{0}: {1}", row.Country, row.Count); } } </code></pre> <p>The TSQL isn't quite what we'd like, but it does the job:</p> <pre><code>SELECT [t1].[Country], ( SELECT COUNT(*) FROM ( SELECT DISTINCT [t2].[City] FROM [dbo].[Customers] AS [t2] WHERE ((([t1].[Country] IS NULL) AND ([t2].[Country] IS NULL)) OR (([t1] .[Country] IS NOT NULL) AND ([t2].[Country] IS NOT NULL) AND ([t1].[Country] = [ t2].[Country]))) AND ([t2].[CustomerID] <> @p0) ) AS [t3] ) AS [Count] FROM ( SELECT [t0].[Country] FROM [dbo].[Customers] AS [t0] WHERE [t0].[CustomerID] <> @p0 GROUP BY [t0].[Country] ) AS [t1] -- @p0: Input NVarChar (Size = 0; Prec = 0; Scale = 0) [] -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.1 </code></pre> <p>The results, however, are correct- verifyable by running it manually:</p> <pre><code> const string sql = @" SELECT c.Country, COUNT(DISTINCT c.City) AS [Count] FROM Customers c WHERE c.CustomerID != '' GROUP BY c.Country ORDER BY c.Country"; var qry2 = ctx.ExecuteQuery<QueryResult>(sql); foreach(var row in qry2) { Console.WriteLine("{0}: {1}", row.Country, row.Count); } </code></pre> <p>With definition:</p> <pre><code>class QueryResult { public string Country { get; set; } public int Count { get; set; } } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload