How to use GroupBy on a sub Group

Question

Maybe I'm going about this all wrong, I'm new to Linq, but if someone could help me out, I'd really appreciate it. Below is my code. I'm trying to do some summarizing on info in a header and detail table. So I can see for each merchant location what the total sales and total number of visits (individual transactions) there are. The detail table will break 1 transaction in the header table into multiple rows, so 1 transaction id can appear multiple times in the detail table.

            var query2 = db.sales_transaction_header
                           .Join(db.sales_transaction_detail,
                                 sth => sth.transaction_id,
                                 std => std.transaction_id,
                                 (sth, std) => new { TransHeader = sth, TransDetail = std })
                       .GroupBy(x => x.TransHeader.merchant_location_id)
                       .Select(g => new MerchantConsumerSalesDTO
                       {
                           TotalSales = g.Sum(x => x.TransDetail.transaction_amount),
                           MerchantId = g.FirstOrDefault().TransHeader.merchant_location_id,
                           TotalVisits = g.GroupBy(x => x.TransHeader.transaction_id).Select(g2 => g2.Count()),
                       })
                       .ToList();

The TotalVisits is giving me all kinds of problems! I originally tried

TotalVisits = g.Select(x => x.TransHeader.transaction_id).Distinct().Count()

But that gave me the inexplicable error:

Unknown column 'Project3.merchant_location_id' in 'where clause'

Without the .Distinct(), it returns the Count() properly, except it's the wrong number! Since I need to count only the distinct values, but with the Distinct() it gives me an error! So I tried using the GroupBy on the sub group. This won't even allow me to compile. I get various errors such as "cannot implicitly convert Ienumerable to int" or "Cannot convert anonymous type to int" - I'm at my wits end! Am I just totally on the wrong track or is this something tiny and simple to fix??

Thanks in advance!

EDIT: Maybe this will help clarify my intent. My table structures are listed below:

sales_transaction_header
    transaction_id
    merchant_location_id
    card
    transaction_date

sales_transaction_detail
    transaction_detail_id
    transaction_id
    transaction_amount
    seat_nbr

So, basically a single transaction can be broken up into multiple detail rows based on the seat_nbr. So the first groupby, if I understand correctly, should only group the merchant id, still leaving multiple rows for individual transactions. I would need another group by to count unique transaction ids. Correct?

Also, my generated query is below:

SELECT `Project2`.`merchant_location_id`, 
       `Project2`.`c1`, 
       `Project2`.`merchant_location_id1`, 
       (SELECT Count(1) AS `A1` 
        FROM   (SELECT DISTINCT `Extent7`.`transaction_id` 
                FROM   `sales_transaction_header` AS `Extent7` 
                       INNER JOIN `sales_transaction_detail` AS `Extent8` 
                               ON `Extent7`.`transaction_id` = 
                                  `Extent8`.`transaction_id` 
                WHERE  `Project2`.`merchant_location_id` = 
                       `Extent7`.`merchant_location_id`) AS 
               `Distinct2`) AS `C2` 
FROM   (SELECT `Apply1`.`merchant_location_id`, 
               `Apply1`.`merchant_location_id` 
                      AS `merchant_location_id1`, 
               (SELECT Sum(`Extent6`.`transaction_amount`) AS `A1` 
                FROM   `sales_transaction_header` AS `Extent5` 
                       INNER JOIN `sales_transaction_detail` AS `Extent6` 
                               ON `Extent5`.`transaction_id` = 
                                  `Extent6`.`transaction_id` 
                WHERE  `Apply1`.`merchant_location_id` = 
                       `Extent5`.`merchant_location_id`) AS 
               `C1` 
        FROM   (SELECT `Distinct1`.`merchant_location_id`, 
                       (SELECT `Extent3`.`transaction_id` 
                        FROM   `sales_transaction_header` AS `Extent3` 
                               INNER JOIN `sales_transaction_detail` AS 
                                          `Extent4` 
                                       ON `Extent3`.`transaction_id` = 
                                          `Extent4`.`transaction_id` 
                        WHERE  `Distinct1`.`merchant_location_id` = 
                               `Extent3`.`merchant_location_id` 
                        LIMIT  1) AS `transaction_id`, 
                       (SELECT `Extent3`.`merchant_location_id` 
                        FROM   `sales_transaction_header` AS `Extent3` 
                               INNER JOIN `sales_transaction_detail` AS 
                                          `Extent4` 
                                       ON `Extent3`.`transaction_id` = 
                                          `Extent4`.`transaction_id` 
                        WHERE  `Distinct1`.`merchant_location_id` = 
                               `Extent3`.`merchant_location_id` 
                        LIMIT  1) AS `MERCHANT_LOCATION_ID1`, 
                       (SELECT `Extent3`.`transaction_date` 
                        FROM   `sales_transaction_header` AS `Extent3` 
                               INNER JOIN `sales_transaction_detail` AS 
                                          `Extent4` 
                                       ON `Extent3`.`transaction_id` = 
                                          `Extent4`.`transaction_id` 
                        WHERE  `Distinct1`.`merchant_location_id` = 
                               `Extent3`.`merchant_location_id` 
                        LIMIT  1) AS `transaction_date`, 
                       (SELECT `Extent3`.`post_date` 
                        FROM   `sales_transaction_header` AS `Extent3` 
                               INNER JOIN `sales_transaction_detail` AS 
                                          `Extent4` 
                                       ON `Extent3`.`transaction_id` = 
                                          `Extent4`.`transaction_id` 
                        WHERE  `Distinct1`.`merchant_location_id` = 
                               `Extent3`.`merchant_location_id` 
                        LIMIT  1) AS `post_date`, 
                       (SELECT `Extent3`.`card_token` 
                        FROM   `sales_transaction_header` AS `Extent3` 
                               INNER JOIN `sales_transaction_detail` AS 
                                          `Extent4` 
                                       ON `Extent3`.`transaction_id` = 
                                          `Extent4`.`transaction_id` 
                        WHERE  `Distinct1`.`merchant_location_id` = 
                               `Extent3`.`merchant_location_id` 
                        LIMIT  1) AS `card_token`, 
                       (SELECT `Extent3`.`transaction_type` 
                        FROM   `sales_transaction_header` AS `Extent3` 
                               INNER JOIN `sales_transaction_detail` AS 
                                          `Extent4` 
                                       ON `Extent3`.`transaction_id` = 
                                          `Extent4`.`transaction_id` 
                        WHERE  `Distinct1`.`merchant_location_id` = 
                               `Extent3`.`merchant_location_id` 
                        LIMIT  1) AS `transaction_type`, 
                       (SELECT `Extent3`.`processed` 
                        FROM   `sales_transaction_header` AS `Extent3` 
                               INNER JOIN `sales_transaction_detail` AS 
                                          `Extent4` 
                                       ON `Extent3`.`transaction_id` = 
                                          `Extent4`.`transaction_id` 
                        WHERE  `Distinct1`.`merchant_location_id` = 
                               `Extent3`.`merchant_location_id` 
                        LIMIT  1) AS `processed`, 
                       (SELECT `Extent3`.`partner_transaction_id` 
                        FROM   `sales_transaction_header` AS `Extent3` 
                               INNER JOIN `sales_transaction_detail` AS 
                                          `Extent4` 
                                       ON `Extent3`.`transaction_id` = 
                                          `Extent4`.`transaction_id` 
                        WHERE  `Distinct1`.`merchant_location_id` = 
                               `Extent3`.`merchant_location_id` 
                        LIMIT  1) AS `partner_transaction_id`, 
                       (SELECT `Extent4`.`transaction_detail_id` 
                        FROM   `sales_transaction_header` AS `Extent3` 
                               INNER JOIN `sales_transaction_detail` AS 
                                          `Extent4` 
                                       ON `Extent3`.`transaction_id` = 
                                          `Extent4`.`transaction_id` 
                        WHERE  `Distinct1`.`merchant_location_id` = 
                               `Extent3`.`merchant_location_id` 
                        LIMIT  1) AS `transaction_detail_id`, 
                       (SELECT `Extent4`.`transaction_id` AS `TRANSACTION_ID1` 
                        FROM   `sales_transaction_header` AS `Extent3` 
                               INNER JOIN `sales_transaction_detail` AS 
                                          `Extent4` 
                                       ON `Extent3`.`transaction_id` = 
                                          `Extent4`.`transaction_id` 
                        WHERE  `Distinct1`.`merchant_location_id` = 
                               `Extent3`.`merchant_location_id` 
                        LIMIT  1) AS `TRANSACTION_ID1`, 
                       (SELECT `Extent4`.`transaction_amount` 
                        FROM   `sales_transaction_header` AS `Extent3` 
                               INNER JOIN `sales_transaction_detail` AS 
                                          `Extent4` 
                                       ON `Extent3`.`transaction_id` = 
                                          `Extent4`.`transaction_id` 
                        WHERE  `Distinct1`.`merchant_location_id` = 
                               `Extent3`.`merchant_location_id` 
                        LIMIT  1) AS `transaction_amount`, 
                       (SELECT `Extent4`.`charity_location_id` 
                        FROM   `sales_transaction_header` AS `Extent3` 
                               INNER JOIN `sales_transaction_detail` AS 
                                          `Extent4` 
                                       ON `Extent3`.`transaction_id` = 
                                          `Extent4`.`transaction_id` 
                        WHERE  `Distinct1`.`merchant_location_id` = 
                               `Extent3`.`merchant_location_id` 
                        LIMIT  1) AS `charity_location_id`, 
                       (SELECT `Extent4`.`event_type_id` 
                        FROM   `sales_transaction_header` AS `Extent3` 
                               INNER JOIN `sales_transaction_detail` AS 
                                          `Extent4` 
                                       ON `Extent3`.`transaction_id` = 
                                          `Extent4`.`transaction_id` 
                        WHERE  `Distinct1`.`merchant_location_id` = 
                               `Extent3`.`merchant_location_id` 
                        LIMIT  1) AS `event_type_id` 
                FROM   (SELECT DISTINCT `Extent1`.`merchant_location_id` 
                        FROM   `sales_transaction_header` AS `Extent1` 
                               INNER JOIN `sales_transaction_detail` AS 
                                          `Extent2` 
                                       ON `Extent1`.`transaction_id` = 
                                          `Extent2`.`transaction_id`) AS 
                       `Distinct1`) AS `Apply1`) AS `Project2`

UPDATE:

Ultimately, I went with:

         var query = db.sales_transaction_header
                       .Join(db.sales_transaction_detail,
                             sth => sth.transaction_id,
                             std => std.transaction_id,
                             (sth, std) => new { TransHeader = sth, TransDetail = std })
                   .GroupBy(x => x.TransHeader.merchant_location_id)
                   .ToList()
                   .Select(g => new MerchantConsumerSalesDTO
                   {
                       TotalSales = g.Sum(x => x.TransDetail.transaction_amount),
                       MerchantId = g.Key,
                       TotalConsumerVisits = g.GroupBy(x => x.TransHeader.transaction_id).Count(),
                   });

This was due to a limitation of the MySql database I was using - it works in MSSQL just fine apparently without having to use ToList(), but at least it's working now! THANKS SO MUCH, ROB!

MerchantId should be `g.Key`. Though they're semantically the same, it's a lot clearer. TotalVisits should simply be `g.Count()` (you've already grouped) or `g.Distinct().Count()`. It might be better to do the distinct before grouping, though. — Rob, Mar 17 '16 at 23:58
in the `.Select` portion of the code what does the compiler say each of those variables are (i.e. `TotalSales`, `MerchantID`, `TotalVisits`)... when you hover over them with the cursor? (assuming you are using VS) — Chef_Code, Mar 18 '16 at 00:10
I believe you are missing a where clause. Check out this [other stackoverflow answer](http://stackoverflow.com/questions/2767709/c-sharp-joins-where-with-linq-and-lambda) for more information. — Chef_Code, Mar 18 '16 at 00:18
@Chef_Code The where is actually included for the method syntax (`sth => sth.transaction_id, std => std.transaction_id` select the keys and generate the `where sth.transaction_id = std.transaction_id` — Rob, Mar 18 '16 at 00:22
@Chef_Code, TotalSales is decimal, MerchantID and TotalVisits are both ints — user2480201, Mar 18 '16 at 00:39

score 0 · Accepted Answer · edited Jan 18 '21 at 12:35

0

After a discussion in chat, it turns out the behaviour is due to a bug in the MySql connector.

The following code gets around this bug by evaluating the group in-memory:

var query = db.sales_transaction_header
    .Join(db.sales_transaction_detail,
        sth => sth.transaction_id,
        std => std.transaction_id,
        (sth, std) => new {TransHeader = sth, TransDetail = std})
    .GroupBy(x => x.TransHeader.merchant_location_id)
    .ToList()
    .Select(g => new MerchantConsumerSalesDTO
    {
        TotalSales = g.Sum(x => x.TransDetail.transaction_amount),
        MerchantId = g.Key,
        TotalConsumerVisits = g.GroupBy(x => x.TransHeader.transaction_id).Count(),
    });

This is because it generates the following code:

SELECT `GroupBy1`.`k1`      AS `merchant_location_id`, 
       `GroupBy1`.`a1`      AS `C1`, 
       (SELECT Count(1) AS `A1` 
        FROM   (SELECT DISTINCT `Extent3`.`transaction_id` 
                FROM   `sales_transaction_header` AS `Extent3` 
                       INNER JOIN `sales_transaction_detail` AS `Extent4` 
                               ON `Extent3`.`transaction_id` = 
                                  `Extent4`.`transaction_id` 
                WHERE  `GroupBy1`.`k1` = `Extent3`.`merchant_location_id`) AS 
               `Distinct1`) AS `C2` 
FROM   (SELECT `Extent1`.`merchant_location_id`    AS `K1`, 
               Sum(`Extent2`.`transaction_amount`) AS `A1` 
        FROM   `sales_transaction_header` AS `Extent1` 
               INNER JOIN `sales_transaction_detail` AS `Extent2` 
                       ON `Extent1`.`transaction_id` = 
                          `Extent2`.`transaction_id` 
        GROUP  BY `Extent1`.`merchant_location_id`) AS `GroupBy1`

In MySql, inner selects cannot refer to outer results, so this line:

`GroupBy1`.`k1` = `Extent3`.`merchant_location_id`

Fails, as GroupBy1 is outside the scope of the select.

The fix is to add ToList() after the group, so that the final select (and summations) are done in-memory

edited Jan 18 '21 at 12:35

Community

1
1

answered Mar 18 '16 at 00:15

Rob

26,989
16
82
98

thanks - actually I tried the last groupby().count() already and it was one of those that gave me the "unknown column merchant_location_id in where clause" error. The TotalVisits is the total count of transaction ids, however, since the transaction ids can appear multiple times even after the first group by (key = merchant id) I still need to weed out the multiples somehow. Is there another way to do this beyond your last suggestion? – user2480201 Mar 18 '16 at 00:36
@user2480201 That's quite strange then - it should be able to handle it. Are you able to post the SQL code that's generated? – Rob Mar 18 '16 at 00:38
Let me also post the tables, maybe that will be clearer. Where can I get the generated code? I know I can look at it during debug in VS, but it doesn't let me select/copy it.. – user2480201 Mar 18 '16 at 00:44
@user2480201 Two ways: 1. Open up SQL Profiler (if your database is local), or 2. Remove the `ToList()` at the end of your query. Put a breakpoint immediately after it, and when you over over the variable, it should show the generated SQL (then right click and copy value) – Rob Mar 18 '16 at 00:45
thanks, I've posted the generated sql...it's a mess though, I'm sorry - not sure how to make it more readable... – user2480201 Mar 18 '16 at 01:01
@user2480201 No worries - I've formatted it :). The SQL looks *vastly* different from the code. I have a feeling `MerchantId` may be generating a bunch of extra code - did you change it to `g.Key` (if not - could you, and supply that SQL?). Also, the line mentioned in the original error is not present - has the error changed? (there's no project3) – Rob Mar 18 '16 at 01:05
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/106667/discussion-between-rob-and-user2480201). – Rob Mar 18 '16 at 01:06
I've never used chat before. I believe I've sent you the generated query via chat, but if you don't see anything please let me know – user2480201 Mar 18 '16 at 01:14
@user2480201 Yep - I've seen it. In chat, you can use @[username] and it will ping them just like a comment - otherwise the message can go unseen – Rob Mar 18 '16 at 01:19

How to use GroupBy on a sub Group

1 Answers1