LINQ with group, count, and select. I have no idea at all.. RRS feed

  • Question

  • Hello, experts
    right now i'm trying to convert this kind of sql query into linq in visual basic, but i get stuck on how to group it..
    Please help.
    SELECT CASE RIGHT(PICName, 3) WHEN '(P)' THEN 'Problem' WHEN '(R)' THEN 'Request' ELSE 'Other' END AS [Requests/Problems], COUNT(RIGHT(PICName, 3)) AS Amount, CONVERT(decimal(18, 2), CONVERT(Decimal(18, 2), COUNT(RIGHT(PICName, 3))) / CONVERT(Decimal(18, 2), (SELECT COUNT(RIGHT(PICName, 3)) FROM Ticket)) * 100) AS [% Amount]
    FROM Ticket

    the result i need is like:
    Requests/Problems Amount % Amount
    Problem 20 20.00
    Request 45 45.00
    Other 35 35.00

    Thank You.
    Saturday, September 25, 2010 2:09 PM

All replies

  • Seeing raw sql and not knowing a database scheme makes this question an almost impossible one to answer.
    William Wegerson (www.OmegaCoder.Com)
    Tuesday, September 28, 2010 2:40 PM
  • var result = (from x in Table
       where x.Id == Id
       group x by x.ForeignId into groups
       let count = x.Count()
       from grouped in groups
       select new
          Count = count,
          X = grouped
       } );
    Donno if this helps.  But here is a grouped counted selected in C#.
    Tuesday, September 28, 2010 5:18 PM
  • hmm..

    could you imagine if i explain it a lil' bit more?

    this query consists only of 1 table named Ticket. the PICname (People in Charge) filed is for storing the staff names who are responsible for the ticket (user problem handling queue) and the code for the category of the ticket ('(P)' for Problems and '(R)' for Request). so the the content of this field is like 'Liana Costa (R)'

    I'd like to group the PICname by the category, which one are Problem (P), Request (R), and Other (if the content doesn't has (P)/(R)). after grouping them, i wanna know how many are (P), (R), and 'Other' and then take the percentage of them

    is it clearer?

    Wednesday, September 29, 2010 2:37 AM
  • hmm... how about the percentage?

    Wednesday, September 29, 2010 2:38 AM