CODEWITHSUNDEEP
sqllinq
gor
gor

Reputation: 11658

Convert tricky linq expression to SQL

I have following linq expression:

        var files = posts
            .Select(post => int.Parse(post.Length.ToString()[0].ToString()))
            .GroupBy(c => c)
            .ToDictionary(g => g.Key, g => g.Count());

I want to translate it to SQL (Microsoft SQL). Where source is IEnumerable<string>. The problem is how to group records by it's first digit and count quantity of items in each group.

The database has one table Posts, that has column Body(varchar(max)). I'm interested in first digit of it's length.

Thanks!

Upvotes: 0

Views: 291

Answers (1)

Amy B
Amy B

Reputation: 110171

SELECT firstDigit as Key, COUNT(*) as theCount
FROM
(
  SELECT convert(int, LEFT(convert(varchar(30), f), 1)) as firstDigit
  FROM Source as f
) as sub
GROUP BY firstDigit

Upvotes: 1

Related Questions