asked on

Vertical Sum For Top 3

Hi Experts!

Thanks for reading this.
This is a challenging one for me; if I could give 1000+ I would.
Supposed you've a table with this content:

Qid        Value
---------- -----
1            0
2            2
3            1
4            4
5            0
6            0
7            1
8            2
9            0
10           4

Open in new window

Here's how to use the content; you want to get A, B, C, D, and E:

A = (Qid of 1) + (Qid of 2) = 0 + 2 = 2
B = (Qid of 3) + (Qid of 4) = 1 + 4 = 5
C = (Qid of 5) + (Qid of 6) = 0 + 0 = 0
D = (Qid of 7) + (Qid of 8) = 1 + 2 = 3
E = (Qid of 9) + (Qid of 10) = 0 + 4 = 4

Open in new window

Ultimately you would like to get the top 3 values.
So, they are: B (5), E (4), and D (3)

The actual logic is like this:

We'll need to do this in LINQ; here's the pseudo code:

Q q1  = new Q { Qid=1 , Value = "0" };
Q q2  = new Q { Qid=2 , Value = "2" };
Q q3  = new Q { Qid=3 , Value = "1" };
Q q4  = new Q { Qid=4 , Value = "4" };
Q q5  = new Q { Qid=5 , Value = "0" };
Q q6  = new Q { Qid=6 , Value = "0" };
Q q7  = new Q { Qid=7 , Value = "1" };
Q q8  = new Q { Qid=8 , Value = "2" };
Q q9  = new Q { Qid=9 , Value = "0" };
Q q10 = new Q { Qid=10, Value = "4" };


List<Q> Qs = new List<Q> { q1, q2, q3, q4, q5, q6, q7, q8, q9, q10};

var results = from q in Qs ................
              select new
			  {
			  A =
			  B =
			  C =
			  D =
			  E =
			  };

var Top3 = from r in results ..............

Open in new window

Any ideas?

Carl Tawn

Try this:

            List<Q> list = new List<Q>
            {
                new Q { Qid = 1, Value = 0},
                new Q { Qid = 2, Value = 2},
                new Q { Qid = 3, Value = 1},
                new Q { Qid = 4, Value = 4},
                new Q { Qid = 5, Value = 0},
                new Q { Qid = 6, Value = 0},
                new Q { Qid = 7, Value = 1},
                new Q { Qid = 8, Value = 2},
                new Q { Qid = 9, Value = 0},
                new Q { Qid = 10, Value = 4}
            };

            var query = from x in list
                        join y in list on (x.Qid + 1) equals y.Qid
                        where x.Qid % 2 == 1
                        select (x.Value + y.Value);

            var results = (from x in query
                           orderby x descending
                           select x).Take(3);

Open in new window

Allan

ASKER

Thanks pretty clever carl_tawn. The top 3 is correct; but we're unable to determine from which of 'A-E' is it associated with. In our example, the top 3 are: B = 5, E = 4, and D = 3

Can you please take a look at my 'query'? Trying to add three Qids to make this:

A= Qid1 + Qid17 + Qid33, B=Qid2 + Qid18 + Qid34, all the way to P.

    List<Q> list = new List<Q>
{
    new Q { Qid = 1, Value = 0},
    new Q { Qid = 2, Value = 2},
    new Q { Qid = 3, Value = 1},
    new Q { Qid = 4, Value = 4},
    new Q { Qid = 5, Value = 0},
    new Q { Qid = 6, Value = 0},
    new Q { Qid = 7, Value = 1},
    new Q { Qid = 8, Value = 2},
    new Q { Qid = 9, Value = 0},
    new Q { Qid = 10, Value = 4},
    new Q { Qid = 11, Value = 0},
    new Q { Qid = 12, Value = 2},
    new Q { Qid = 13, Value = 1},
    new Q { Qid = 14, Value = 4},
    new Q { Qid = 15, Value = 0},
    new Q { Qid = 16, Value = 0},
    new Q { Qid = 17, Value = 1},
    new Q { Qid = 18, Value = 2},
    new Q { Qid = 19, Value = 0},
    new Q { Qid = 20, Value = 4},
    new Q { Qid = 21, Value = 0},
    new Q { Qid = 22, Value = 2},
    new Q { Qid = 23, Value = 1},
    new Q { Qid = 24, Value = 4},
    new Q { Qid = 25, Value = 0},
    new Q { Qid = 26, Value = 0},
    new Q { Qid = 27, Value = 1},
    new Q { Qid = 28, Value = 2},
    new Q { Qid = 29, Value = 0},
    new Q { Qid = 30, Value = 4},
    new Q { Qid = 31, Value = 0},
    new Q { Qid = 32, Value = 2},
    new Q { Qid = 33, Value = 1},
    new Q { Qid = 34, Value = 4},
    new Q { Qid = 35, Value = 0},
    new Q { Qid = 36, Value = 0},
    new Q { Qid = 37, Value = 1},
    new Q { Qid = 38, Value = 2},
    new Q { Qid = 39, Value = 0},
    new Q { Qid = 40, Value = 4},
    new Q { Qid = 41, Value = 0},
    new Q { Qid = 42, Value = 2},
    new Q { Qid = 43, Value = 1},
    new Q { Qid = 44, Value = 4},
    new Q { Qid = 45, Value = 0},
    new Q { Qid = 46, Value = 0},
    new Q { Qid = 47, Value = 1},
    new Q { Qid = 48, Value = 2}
};

    var query = from x in list
                join y in list on (x.Qid + 16) equals y.Qid
                join z in list on (x.Qid + 32) equals z.Qid
                select (x.Value + y.Value + z.Value);

    var results = (from x in query
                   orderby x descending
                   select x).Take(3);

Open in new window

Is there a way to tell from which logic (A-P) the top 3 came from?

Carl Tawn

This should do what you want:

var query = (from x in list
            join y in list on (x.Qid + 16) equals y.Qid
            join z in list on (x.Qid + 32) equals z.Qid
            where x.Qid <= 16
            select x.Value + y.Value + z.Value)
    .Select((item, index) => new { Letter = (char)(65 + index), Value = (int)item });

var results = (from x in query
                      orderby x.Value descending
                       select x).Take(3);

foreach(var item in results)
{
    Console.WriteLine(item.Letter + "=" + item.Value);
}

Open in new window

Allan

ASKER

Thanks carl_tawn; came across this in results:

LINQ to Entities does not recognize the method 'System.Linq.IQueryable`1[<>f__AnonymousType5`2[System.Char,System.Int32]] Select[Int32,<>f__AnonymousType5`2](System.Linq.IQueryable`1[System.Int32], System.Linq.Expressions.Expression`1[System.Func`3[System.Int32,System.Int32,<>f__AnonymousType5`2[System.Char,System.Int32]]])' method, and this method cannot be translated into a store expression.

The example above is using local values, but what I'm doing is querying two tables in a database:

var answered = from a in db.tbl01
                join v in db.tbl01
                on a.ScaleID equals v.tScaleID
                where a.UserID == myUser.UserID
                select new
                {
                    Qid = a.QuestionID,
                    Value = v.ScaleValue
                };


var query = (from x in answered
                join y in answered on (x.Qid + 16) equals y.Qid
                join z in answered on (x.Qid + 32) equals z.Qid
                where x.Qid <= 16
        select x.Value + y.Value + z.Value)
        .Select((item, index) => new { Letter = (char)(65 + index), Value = item });


var results = (from x in query
                orderby x.Value descending
                select x).Take(3);

Open in new window

Qid and Value are Int data type.

I tried and cannot get it to work. Thanks again for your help!

ASKER CERTIFIED SOLUTION

Carl Tawn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Allan

ASKER

Thank you carl_tawn; I saved time and learned something new from you!