Question Sum based on a criteria

[raysefo]

Hi there,

I have a console app which I am getting results from a database. I want to sum the unitPrice and totalPrice by grouping the 3rd and 4th character of shopNo (2nd column: 98184487 ). Is there a practical way to do it?

Here is the query result sample:

referenceid    shopno    safeno    cashierno    purchaseStatusDate    confirmcanceldatetime    productcode    productdescription    quantity    unitprice    totalprice    status
031A482E-6C02-419D-B8A1-0F145DA2F079    98184487    3    9027765    2020-04-01 14:23:00.000    2020-04-01 14:24:23.733    000000001573    Bim 840 Riot Points    1    32    32    2
ED4AE4C3-5A80-49E3-AD81-1BC20C86C302    96174860    1    167834    2020-04-01 19:42:28.850    2020-04-01 19:42:36.887    000000001585    2100 ZA    1    5    5    1
9D6ECB8E-3080-44EB-8155-1EE11064C506    96173728    1    194044    2020-04-01 14:24:22.000    NULL    000000001573    Bim 840 Riot Points    1    32    32    0
2D87E61E-3281-46BC-A433-247B683D5715    98184487    3    9027765    2020-04-01 14:16:35.000    2020-04-01 14:17:26.260    000000001570    10 TL'lik Steam Bakiyesi    1    10    10    1
AD7E534A-7084-4B9F-B2DD-24F2483B703B    96113528    1    176849    2020-04-01 13:47:41.047    2020-04-01 13:47:48.960    000000001570    10 TL'lik Steam Bakiyesi    1    10    10    1
36B30CCD-B843-44C1-AEA2-283625D4ECD0    98104487    3    9027765    2020-04-01 14:22:04.543    2020-04-01 14:22:23.940    000000001585    2100 ZA    1    5    5    1
721E9139-7D25-4753-8AA9-36356DF2CE08    96173761    1    150578    2020-04-01 17:44:55.000    2020-04-01 17:44:55.787    000000001570    10 TL'lik Steam Bakiyesi    1    10    10    1
F9143D45-40BF-4471-8579-391947BA1D7D    97114860    2    9026698    2020-04-01 16:19:24.000    2020-04-01 16:19:19.427    000000001570    10 TL'lik Steam Bakiyesi    1    10    10    1
D992C1FC-4CC5-42D1-8176-3D1C89018EF2    97174860    2    9026698    2020-04-01 16:17:51.000    2020-04-01 16:17:46.613    000000001570    10 TL'lik Steam Bakiyesi    1    10    10    1
8B52A941-4F5E-41BE-A9EB-4059BF09F794    96101615    1    133109    2020-04-01 16:51:25.957    2020-04-01 16:51:37.163    000000001585    2100 ZA    1    5    5    1
1DF20FEB-6921-4010-B8B1-445EB8A314AC    96173528    1    176849    2020-04-01 13:48:24.000    2020-04-01 13:48:22.310    000000001570    10 TL'lik Steam Bakiyesi    1    10    10    1

Best Regards.

 

[jmcilhinney]

Are you wanting to do the aggregation in SQL or in C#? If in SQL, we need to know what database to ensure that we get the correct syntax for manipulating the text value. Also, can I conform that that shopno column is text and not numeric?

 

[raysefo]

Actually I tried and also asked in a SQL forum but I think it is hard to achieve with SQL query. So in C#, I would like to implement a handy solution for this. ShopNo is text by the way.

 

[jmcilhinney]

I would think that it would be easier in SQL, e.g. for SQL Server:

SELECT SUBSTRING(shopno, 3, 2) AS shopnoSubstring, SUM(unitPrice) AS unitPriceSum, SUM(totalPrice) AS totalPriceSum
FROM myTable
GROUP BY SUBSTRING(shopno, 3, 2)

 

[raysefo]

But I want all the other columns in my solution

 

[jmcilhinney]

But I want all the other columns in mu solution

You clearly don't understand how grouping works. Consider these two rows:

031A482E-6C02-419D-B8A1-0F145DA2F079  98184487  3  9027765  2020-04-01 14:23:00.000  2020-04-01 14:24:23.733  000000001573  Bim 840 Riot Points       1  32  32  2
2D87E61E-3281-46BC-A433-247B683D5715  98184487  3  9027765  2020-04-01 14:16:35.000  2020-04-01 14:17:26.260  000000001570  10 TL'lik Steam Bakiyesi  1  10  10  1

According to your original question, those two rows should be combined into one row and the unitPrice and totalPrice columns summed. What exactly do you expect to happen to the rest of the columns? Some of them have the same value but some have different values, so how exactly do you think those different values from the original two rows should be combined into a single value in the final row?

 

[raysefo]

031A482E-6C02-419D-B8A1-0F145DA2F079  98184487  3  9027765  2020-04-01 14:23:00.000  2020-04-01 14:24:23.733  000000001573  Bim 840 Riot Points       1  32  32  2
2D87E61E-3281-46BC-A433-247B683D5715  98184487  3  9027765  2020-04-01 14:16:35.000  2020-04-01 14:17:26.260  000000001570  10 TL'lik Steam Bakiyesi  1  10  10  1
[B]TotalPrice: 42 (32+10)[/B]

Which is why I am trying to implement it in C#. Basically I would like to group results based on shopNo 3rd and 4th characters and sum their prices. Then maybe I am gonna write them into a file or send an e-mail which is not the subject of this discussion.

 

[jmcilhinney]

Perhaps you ought to provide a FULL and CLEAR explanation of the problem up front, so we know what we're actually being asked for and won't waste time providing something else. You would most efficiently do it using a LINQ query but I'm not inclined to work out how right now. It's 3:30 AM here and I rarely use grouping in LINQ so I would have to think about it a bit and test a bit. I'm too tired for that right now. If someone else hasn't done it by the time I'm back online, I'll have a got then.

 

[raysefo]

The full story is, a client wants me to write daily reconciliation (similar to the above results in my first post) into a file. Now he wants me to add the subtotals in that file based on shopNO. (3rd and 4th characters identifies the regions) Thank you and I appreciate your effort by the way.

 

[NoUserHere]

But I want all the other columns in my solution

And how was that not relevant to mention in your opening topic, and why are you only mentioning that now?

You clearly don't understand how grouping works. Consider these two rows:

031A482E-6C02-419D-B8A1-0F145DA2F079  98184487  3  9027765  2020-04-01 14:23:00.000  2020-04-01 14:24:23.733  000000001573  Bim 840 Riot Points       1  32  32  2
2D87E61E-3281-46BC-A433-247B683D5715  98184487  3  9027765  2020-04-01 14:16:35.000  2020-04-01 14:17:26.260  000000001570  10 TL'lik Steam Bakiyesi  1  10  10  1

According to your original question, those two rows should be combined into one row and the unitPrice and totalPrice columns summed. What exactly do you expect to happen to the rest of the columns? Some of them have the same value but some have different values, so how exactly do you think those different values from the original two rows should be combined into a single value in the final row?

I'm not sure if JM is misunderstanding your question, (as am I), since you wrote it with extreme vagueness. And on post 9 you still aren't being clear about your aims, and I think that is because you don't understand what your client is asking you to do. And if you do understand your clients instructions. You're not doing a lot to help us help you. Put some effort into explaining what you're trying and wanting to do.

It's 3:30 AM here and I rarely use grouping in LINQ so I would have to think about it a bit and test a bit.

Get some sleep JM, these late nights certainly catch up with us, and trust me they very easily derail your health, as I've recently found out.

@raysefo This would be easier to do in SQL. If your client will allow you to modify the database, it would be helpful of him to help you by allowing you have a table that you can select your fields into. Ie SELECT INTO and with that, use Linq as a filter to finish off your query by selecting the relevant data from the new table. And when done, you could always drop the data in the table when no longer needed. If that is not something your client will allow, you are looking at doing this with two Linq queries. Sorry, but I am also not in a position at present to write it out for you.

 

[JohnH]

A linq example with a strongly typed DataTable, result 'groups' can be looped, and Rows of each group can be looped. You can also use ToDictionary if you want to get one group by key.

var groups = from row in table.AsEnumerable()
             group row by row.shopno.Substring(2, 2) into g
             select new { g.Key, Rows = g.ToList(), Total = g.Sum(item => item.totalprice) };

 

[raysefo]

Thank you @JohnH, How can I use it in my console app?

class Test
    {
        static readonly log4net.ILog log =
            log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        static void Main(string[] args)
        {
            try
            {

                //Declarations
                DateTime runday = DateTime.Today;
                string status = null;
                string myFileName = String.Format("{0}__{1}", DateTime.Now.ToString("ddMMyyyy"), "ReconFile.txt");
                //DEV
                string myFullPath = Path.Combine("T:\\ReconLogFiles\\", myFileName);
                

                if (args.Length > 0)
                {
                    Console.WriteLine(args[0].ToString());
                    runday = DateTime.Parse(args[0].ToString());
                }

                // Create the connection to the resource!
                // This is the connection, that is established and
                // will be available throughout this block.
                using (var conn = new SqlConnection())
                {
                    // Create the connectionString
                    //DEV
                    conn.ConnectionString =
                        "Server=(localdb)\\MSSQLLocalDB; Initial Catalog=TEST; Integrated Security=True; MultipleActiveResultSets=True; Trusted_Connection=yes;";
                    conn.Open();
                    
                    
                   var command = new SqlCommand(
                        "SELECT cf.referenceid, ISNULL(cc.shopno,gr.shopNo) AS shopno, ISNULL(cc.safeno,gr.safeNo) AS safeno, ISNULL(cc.cashierno, gr.cashierNo) AS cashierno, cf.purchaseStatusDate, cc.confirmcanceldatetime, cf.productcode, cf.productdescription, cf.quantity, cf.unitprice, cf.totalprice, ISNULL(cc.status, 0) As status FROM[gameconfirmresponses] cf LEFT JOIN(SELECT *, Row_number() OVER(partition BY referenceid ORDER BY confirmcanceldatetime) AS row_num FROM[confirmcancels]) AS cc ON cf.referenceid = cc.referenceid AND cc.row_num = 1 JOIN[GameRequests] AS gr ON gr.referenceId = cf.referenceId WHERE cf.purchasestatusdate >= @Today AND cf.purchasestatusdate < DATEADD(day,1,@Today)",
                        conn);
                      
                    // Add the parameters.
                    command.Parameters.Add(new SqlParameter("Today", runday));

                  
                    using (var reader = command.ExecuteReader())
                    {
                        
                        while (reader.Read())
                        {

                            Console.WriteLine(
                                $"{reader[0]} \t | {reader[1]} \t | {reader[2]} \t | {reader[3]} \t | {reader[4]} \t | {reader[5]} \t | {reader[6]} \t | {reader[7]} \t | {reader[8]} \t | {reader[9]} \t | {reader[10]} \t | {reader[11]}");

                            if (reader.GetInt32(11) == 0)
                            {
                                status = "Uncertain";
                            }
                            else if (reader.GetInt32(11) == 1)
                            {
                                status = "Confirm";
                            }
                            else
                            {
                                status = "Cancel";
                            }

                            log.Info(
                                $"{reader[0]};{reader[1]};{reader[2]};{reader[3]};{reader[4]};{reader[5]};{reader[6]};{reader[7]};{reader[8]};{reader[9]};{reader[10]}" +
                                ";" + status);

                            // Append text to an existing file.
                            using (StreamWriter outputFile = new StreamWriter(myFullPath, true))
                            {
                                outputFile.WriteLine(
                                    $"{reader[0]};{reader[1]};{reader[2]};{reader[3]};{reader[4]};{reader[5]};{reader[6]};{reader[7]};{reader[8]};{reader[9]};{reader[10]}" +
                                    ";" + status);
                            }

                            Console.WriteLine("Done! Press enter to move to the next step");
                        }

                        //No Purchase
                        if (!reader.HasRows)
                        {
                            using (StreamWriter outputFile = new StreamWriter(myFullPath, false))
                            {
                            }
                        }
                    }

                    Console.WriteLine("Data displayed! Now press enter to move to the next section!");
                }
            }
            catch (Exception ex)
            {
                log.Error("Error Message: " + ex.Message.ToString(), ex);
            }
        }
    }

 

[JohnH]

Start with adding a typed dataset instead of manual database code: Create and configure datasets - Visual Studio

 

[Skydiver]

But I want all the other columns in my solution

To echo what the others have said, so what is the sum of these two column values?

Bim 840 Riot Points
10 TL'lik Steam Bakiyesi

I think that you need to think through what you are asking for and/or doing.

 

[raysefo]

Here is the result I want;

2a5a27ae-d6da-4bd0-8508-d0a5606708e0;98004487;CENK;CENK;5.04.2020 09:31:51;;000000001585;ZULA;1;5;5;Uncertain
Subtotal: 5
b6a1683c-82aa-47dc-b2e4-0b54be5fb3c4;98154487;22;33;5.04.2020 15:53:16;5.04.2020 15:53:21;000000001572;Bim 400 Riot Points;1;12;12;Confirm
Subtotal: 12
6d7ee122-8a73-464f-be2a-18ef9133e77d;98164487;22;33;5.04.2020 15:54:26;5.04.2020 15:54:28;000000001574;25 Hükümdarlık;1;6;6;Confirm
6ea82dbd-f82f-40e7-9d91-ddef42fb6c09;98164487;CENK;CENK;5.04.2020 15:54:15;;000000001573;Bim 840 Riot Points;1;24;24;Uncertain
Subtotal: 30
2ffb3e5e-bff4-4595-bdf5-b6e34902de58;98174487;22;33;5.04.2020 15:56:46;5.04.2020 15:56:56;000000001570;10 TL'lik Steam Bakiyesi;1;10;10;Confirm
1e0f954e-b589-44e1-9956-22cf89fbf864;98174487;22;33;5.04.2020 15:55:14;5.04.2020 15:56:08;000000001575;50 Hükümdarlık;1;12;12;Cancel
Subtotal: 22
68151ed5-28db-4ade-aae1-428562f38fad;98204487;CENK;CENK;5.04.2020 09:32:07;;000000001585;ZULA;1;5;5;Uncertain
Subtotal: 5
16b539f0-a908-4f4e-b8b8-b05f8cba0191;98404487;22;33;5.04.2020 15:53:48;5.04.2020 15:53:49;000000001573;Bim 840 Riot Points;1;24;24;Confirm
Subtotal: 24
Grand total: 98

Here is how I did it;

class Program
    {
        static readonly log4net.ILog log =
            log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        static void Main(string[] args)
        {
            try
            {

                //Declarations
                DateTime runday = DateTime.Today;
                string status = null;
                string myFileName = String.Format("{0}__{1}", DateTime.Now.ToString("ddMMyyyy"), "RegionalReconFile.txt");
                //DEV
                string myFullPath = Path.Combine("T:\\ReconLogFiles\\", myFileName);
                
                if (args.Length > 0)
                {
                    Console.WriteLine(args[0].ToString());
                    runday = DateTime.Parse(args[0].ToString());
                }

                using (var conn = new SqlConnection())
                {
                    // Create the connectionString
                    
                    //DEV
                    conn.ConnectionString =
                        "Server=(localdb)\\MSSQLLocalDB; Initial Catalog=TestAPI; Integrated Security=True; MultipleActiveResultSets=True; Trusted_Connection=yes;";
                    
                    conn.Open();
                  
                    var command = new SqlCommand(
                        "SELECT cf.referenceid, ISNULL(cc.shopno,gr.shopNo) AS shopno, ISNULL(cc.safeno,gr.safeNo) AS safeno,ISNULL(cc.cashierno, gr.cashierNo) AS cashierno, cf.purchaseStatusDate, cc.confirmcanceldatetime, cf.productcode, cf.productdescription, cf.quantity, cf.unitprice, cf.totalprice,ISNULL(cc.status, 0) As status, substring(ISNULL([cc].[shopNo], [gr].[shopNo]), 3, 2) as shop FROM[gameconfirmresponses] cf LEFT JOIN(SELECT *, Row_number() OVER(partition BY referenceid ORDER BY confirmcanceldatetime) AS row_num FROM[confirmcancels]) AS cc ON cf.referenceid = cc.referenceid AND cc.row_num = 1 JOIN[GameRequests] AS gr ON gr.referenceId = cf.referenceId WHERE cf.purchasestatusdate >= @Today AND cf.purchasestatusdate < DATEADD(day,1,@Today) order by substring(ISNULL([cc].[shopNo], [gr].[shopNo]),3,2) asc", conn);
                    // Add the parameters.

                    command.Parameters.Add(new SqlParameter("Today", runday));

                    var shop = "";
                    var total = 0.0;
                    var count = 0;
                    var grandTotal = 0.0;
                    using (var reader = command.ExecuteReader())
                    {
                      
                        while (reader.Read())
                        {

                            Console.WriteLine(
                                $"{reader[0]} \t | {reader[1]} \t | {reader[2]} \t | {reader[3]} \t | {reader[4]} \t | {reader[5]} \t | {reader[6]} \t | {reader[7]} \t | {reader[8]} \t | {reader[9]} \t | {reader[10]} \t | {reader[11]} \t | {reader[12]}");

                            if (reader.GetInt32(11) == 0)
                            {
                                status = "Uncertain";
                            }
                            else if (reader.GetInt32(11) == 1)
                            {
                                status = "Confirm";
                            }
                            else
                            {
                                status = "Cancel";
                            }

                           log.Info(
                                $"{reader[0]};{reader[1]};{reader[2]};{reader[3]};{reader[4]};{reader[5]};{reader[6]};{reader[7]};{reader[8]};{reader[9]};{reader[10]}" +
                                ";" + status);

                            // Append text to an existing file.
                            using (StreamWriter outputFile = new StreamWriter(myFullPath, true))
                            {
                              
                              
                               if (shop != reader.GetString(12))
                               {
                                   shop = reader.GetString(12);

                                   if (count > 0 )
                                   {
                                       outputFile.WriteLine(
                                           $"Subtotal: " + total);
                                       grandTotal += total;
                                       total = 0.0;
                                       outputFile.WriteLine(
                                           $"{reader[0]};{reader[1]};{reader[2]};{reader[3]};{reader[4]};{reader[5]};{reader[6]};{reader[7]};{reader[8]};{reader[9]};{reader[10]}" +
                                           ";" + status);
                                    }
                                   else
                                   {
                                       outputFile.WriteLine(
                                           $"{reader[0]};{reader[1]};{reader[2]};{reader[3]};{reader[4]};{reader[5]};{reader[6]};{reader[7]};{reader[8]};{reader[9]};{reader[10]}" +
                                           ";" + status);
                                    }

                                }
                               else
                               {
                                   outputFile.WriteLine(
                                       $"{reader[0]};{reader[1]};{reader[2]};{reader[3]};{reader[4]};{reader[5]};{reader[6]};{reader[7]};{reader[8]};{reader[9]};{reader[10]}" +
                                       ";" + status);
                                  
                                }
                              
                            }
                            //Sum
                            total = total + reader.GetDouble(10);
                            count++;

                            Console.WriteLine("Done! Press enter to move to the next step");
                        }
                        //Last subtotal and grand total
                        
                        using (StreamWriter sw = File.AppendText(myFullPath))
                        {
                            sw.WriteLine($"Subtotal: " + total);
                            grandTotal += total;
                            sw.WriteLine($"Grand total: " + grandTotal);

                        }
                        

                        //No Purchase - empty file
                        if (!reader.HasRows)
                        {
                            using (StreamWriter outputFile = new StreamWriter(myFullPath, false))
                            {
                            }
                        }
                    }

                    Console.WriteLine("Data displayed! Now press enter to move to the next section!");
                }
            }
            catch (Exception ex)
            {
                log.Error("Error Message: " + ex.Message.ToString(), ex);
            }
        }
    }