How can I group with multiple columns and count?

bdwixx

Firstly, I just cannot explain my question in the title properly, but I don't think its a duplicate. I have a List of model which id like to group by multiple fields, then count them into new field, and keep all the other field in the result. I am sorry for the bad explanation, I've been researching how to do this for hours and I just cannot figure it out.. I would like to get from this :

SaleID Name Product Address Phone PrintType
112 Joe Apple New street 12 11223344 PrintType1
112 Joe Apple New street 12 11223344 PrintType2
112 Joe Apple New street 12 11223344 PrintType2
112 Joe Apple New street 12 11223344 PrintType2
112 Joe Apple New street 12 11223344 PrintType3
113 Joe Kiwi New street 12 11223344 PrintType3
114 Jane Orange New street 19 72754722 PrintType1
115 John Orange New street 11 99236527 PrintType2
115 John Orange New street 11 99236527 PrintType2

Grouped by SaleID and PrintType into Something like this :

SaleID Name Product Address Phone NoOfPrintType1 NoOfPrintType2 NoOfPrintType3
112 Joe Apple New street 12 11223344 1 3 1
113 Joe Kiwi New street 12 11223344 0 0 1
114 Jane Orange New street 19 72754722 1 0 0
115 John Orange New street 11 99236527 0 2 0

Preferably id like to use LINQ, but id be good with SQL too, id just like to avoid using a for loop if possible. Edit.: There are a set number of printtypes so it wouldn't need to be dynamic.

Markus

In order to aggregate the data for a set number of PrintTypes, you can group by SaleId as the additional data like address, phone and product are the same for a SaleId (based on your sample data).

var aggregated = from x in GenerateSales() 
    group x by x.SaleId into g // Assume that SaleId is the key that you want to group by
    select new Aggregate() 
    { 
        SaleId = g.Key, 
        // Additional data that are the same for all rows with the same SaleId
        Name = g.First().Name, 
        Product = g.First().Product, 
        Address = g.First().Address, 
        Phone = g.First().Phone, 
        // Calculate counts of Print Types
        NoOfPrintType1 = g.Where(x => x.PrintType == "PrintType1").Count(),
        NoOfPrintType2 = g.Where(x => x.PrintType == "PrintType2").Count(),
        NoOfPrintType3 = g.Where(x => x.PrintType == "PrintType3").Count(),
    };

The Linq statement first groups by SaleId and then creates an object for each SaleId, that comprises

  • the SaleId
  • the additional data like address, phone...
  • the count for each known PrintType (calculated by filtering the items of the group and then counting the rows)

Below you can find a sample that generates test data and outputs the result.

Result

112 | Joe | Apple | New street 12 | 11223344 | 1 | 3 | 1
113 | Joe | Kiwi | New street 12 | 11223344 | 0 | 0 | 1
114 | Jane | Orange | New street 19 | 72754722 | 1 | 0 | 0
115 | John | Orange | New street 11 | 99236527 | 0 | 2 | 0

Sample code

using System;
using System.Collections.Generic;
using System.Linq;

namespace LinqTest
{
    class Sale 
    {
        public string SaleId { get; set; }
        public string Name { get; set; }
        public string Product { get; set; }
        public string Address { get; set; }
        public string Phone { get; set; }
        public string PrintType { get; set; }
    }

    class Aggregate 
    {
        public string SaleId { get; set; }
        public string Name { get; set; }
        public string Product { get; set; }
        public string Address { get; set; }
        public string Phone { get; set; }
        public int NoOfPrintType1 { get; set; }
        public int NoOfPrintType2 { get; set; }
        public int NoOfPrintType3 { get; set; }

        public override string ToString() 
        {
            return $"{SaleId} | {Name} | {Product} | {Address} | {Phone} | {NoOfPrintType1} | {NoOfPrintType2} | {NoOfPrintType3}";
        }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var aggregated = from x in GenerateSales() 
                group x by x.SaleId into g // Assume that SaleId is the key that you want to group by
                select new Aggregate() 
                { 
                    SaleId = g.Key, 
                    // Additional data that are the same for all rows with the same SaleId
                    Name = g.First().Name, 
                    Product = g.First().Product, 
                    Address = g.First().Address, 
                    Phone = g.First().Phone, 
                    // Calculate counts of Print Types
                    NoOfPrintType1 = g.Where(x => x.PrintType == "PrintType1").Count(),
                    NoOfPrintType2 = g.Where(x => x.PrintType == "PrintType2").Count(),
                    NoOfPrintType3 = g.Where(x => x.PrintType == "PrintType3").Count(),
                };
            foreach(var a in aggregated)
            {
                Console.WriteLine(a.ToString());
            }
        }

        static IEnumerable<Sale> GenerateSales()
        {
            yield return new Sale() { SaleId = "112", Name = "Joe", Product = "Apple", Address = "New street 12", Phone = "11223344", PrintType = "PrintType1" };
            yield return new Sale() { SaleId = "112", Name = "Joe", Product = "Apple", Address = "New street 12", Phone = "11223344", PrintType = "PrintType2" };
            yield return new Sale() { SaleId = "112", Name = "Joe", Product = "Apple", Address = "New street 12", Phone = "11223344", PrintType = "PrintType2" };
            yield return new Sale() { SaleId = "112", Name = "Joe", Product = "Apple", Address = "New street 12", Phone = "11223344", PrintType = "PrintType2" };
            yield return new Sale() { SaleId = "112", Name = "Joe", Product = "Apple", Address = "New street 12", Phone = "11223344", PrintType = "PrintType3" };
            yield return new Sale() { SaleId = "113", Name = "Joe", Product = "Kiwi", Address = "New street 12", Phone = "11223344", PrintType = "PrintType3" };
            yield return new Sale() { SaleId = "114", Name = "Jane", Product = "Orange", Address = "New street 19", Phone = "72754722", PrintType = "PrintType1" };
            yield return new Sale() { SaleId = "115", Name = "John", Product = "Orange", Address = "New street 11", Phone = "99236527", PrintType = "PrintType2" };
            yield return new Sale() { SaleId = "115", Name = "John", Product = "Orange", Address = "New street 11", Phone = "99236527", PrintType = "PrintType2" };
        }
    }
}

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Can I count multiple columns with Group By?

Pandas dataframe, how can I group by multiple columns and apply sum for specific column and add new count column?

How do I count multiple group by columns in mysql?

How can I separate two columns of count by group by?

How can I count multiple foreign keys and group them in a row?

How can I count the nominal data from multiple columns

How can I group and sum multiple columns in CSV file?

How can I group rows into columns with multiple values?

How can I group multiple columns and sum the last one?

How to use group by for multiple columns with count?

How to count and group multiple columns in R dataframe?

Group By Multiple columns and count it

how can I split a dataframe by two columns and count number of rows based on group more efficient

How can i join a table with another, then count not empty columns and group them by two other fields?

Group rows in multiple columns and count

r: group by multiple columns and count

Count by group across multiple columns

SQL GROUP BY and COUNT with multiple columns

python group by and count() multiple columns

How can I group into months a column count?

How can I count unique values by columns

How can I count columns in phalcon volt?

how I can count signs columns?

How can I group row values as columns?

How can I count the number of rows in multiple datasets, group them by one column (month), in a Select statement in Bigquery?

How can I query multiple columns from a drop down menu and a count of a certain column

How can I group multiple columns into an object when making a database call in Laravel?

In MySQL how can I group multiple outputted rows into single rows keeping their respective columns

How can I calculate the percentage change within a group for multiple columns in R?