Split List by grouped Data

Question

I have a list of product records that I am displaying on a dataTable, that are grouped by department name / id. From the image below my returned data does not include the highlighted rows. I am trying to achieve whats shown in the image in that - for each set of products already grouped by department (deserts, stationery, household) get the totals, and name of department (and other columns which I removed to show less clutter) to append to the list

I am trying to achieve the outcome above

This is my current code/query which returns this data without the highlighted rows

List<SaleDetailViewModel> list = new List<SaleDetailViewModel>();

NpgsqlCommand query = new NpgsqlCommand
     ("SELECT  q_department.q_name, q_saledetail.q_code, q_saledetail.q_description, " +
     "SUM(q_quantity) AS qtysum,  " + //running total of q_quantity for the product in that department     
     "FROM q_saledetail " +                    
     "LEFT JOIN q_department ON (q_saledetail.q_departmentid = q_department.q_code ) " +     
     "GROUP BY " +
     "q_saledetail.q_departmentid, q_department.q_name, q_saledetail.q_description, q_saledetail.q_code " +
     "ORDER BY q_saledetail.q_departmentid "
     , connectToDB);

NpgsqlDataReader read = query.ExecuteReader();

while(read.Read())
{
     var reportData = new SaleDetailViewModel();

     reportData.departmentName = read["q_name"].ToString(); //department name
     reportData.q_description = read["q_description"].ToString(); //product DESCRIPTION
     reportData.q_code = read["q_code"].ToString(); //product BAR CODE
     reportData.sumOfQuantity = Convert.ToDecimal(read["qtysum"]); //sum of quantity sold for that product


     list.Add(reportData);
}

connectToDB.Close();
return list;

My challenge now is adding the grouped department row data for each set of products, and append to the list that will be returned i.e

//--
foreach(grouped dataset by department)
{
     //get the totals and heading for each set of data
     reportData.q_code = //insert department id
     reportData.q_description = //insert department name
     reportData.sumOfQuantity = // insert total for that department

     list.Add(reportData) //add that to the list that will be shown on view
}
//--

I can get department id and code for the grouped data using read.GetString().

string deptName = read.GetString(0);

How do I continue to loop through the dataReader and add totals for each overall set like the quantity columns and have that as a row of its own to be added to the list?... to get the result from the image shown.

Attempt to explain a bit better: Within my while loop how do I aggregate the set for each department group of products and create a row to add to the list. Or there's a better way of achieving this..

Answer 1

Option 1: Sql and union.

 SELECT  
    q_department.q_name,
    q_saledetail.q_code, 
    q_saledetail.q_description, 
     SUM(q_quantity) AS qtysum, --running total of q_quantity for the product in that department     
     FROM q_saledetail 
     LEFT JOIN q_department ON (q_saledetail.q_departmentid = q_department.q_code ) 
     GROUP BY 
     q_saledetail.q_departmentid, q_department.q_name, q_saledetail.q_description, q_saledetail.q_code


 UNION

 SELECT 
    q_department.q_name,
    '',
    '',
    sum(q_quantity)
    FROM q_saledetail 
     LEFT JOIN q_department ON (q_saledetail.q_departmentid = q_department.q_code ) 
    GROUP BY 
     q_saledetail.q_departmentid, q_department.q_name

     ORDER BY q_saledetail.q_departmentid 

By Removing some of the fields that identify different q_saledetail from the group by list, the sum function will sum for all departments instead of breaking the sums up.

Sorry but I can't remember exactly how ordering works with union statements off hand, but I know you can only order in one place, either the last statement, or the first, or with an outer query that selects from the union.

What you'll have in your data now, is rows that are the 'total' rows for each department, that you know are 'total' rows because the q_code or q_description fields are blank. You can then order the data set by department and then by code (asc or desc) so that the empty code field is at the end of the group of that department and display it like that.

Option 2: Linq and a little manual code work

List<SaleDetailViewModel> list = new List<SaleDetailViewModel>();

while(read.Read())
{
    ... //Your mapping code
     list.Add(reportData);
}
var groupedList = list.GroupBy(x => x.departmentName);

Which will give you a IEnumerable<IGrouping<string, SaleDetailViewModel>.

What that is a your data now grouped by the name of each department which acts as a key You'll now loop through groupedList and add the values of that IGrouping up.

foreach(var group in groupedList)
{
    var total = 0;
    foreach(var saledetail in group)
    {
        total += saledetail.sumOfQuantity;
    }


}

Then you just need to add the logic to add it to a list/your datatable in the order of those groups.

Hope this helps.