locked
How to sum based on a criteria RRS feed

  • Question

  • User-1104215994 posted

    Hello guys,

    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). 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
    17F62645-6BA0-4270-B17C-00041CCC92C0	96171063	1	190129	2020-04-01 15:00:14.000	2020-04-01 15:01:16.143	000000001573	Bim Points	1	32	32	1
    539F7E12-8E9C-4F45-8AA0-027AF46E2C38	97173711	2	9019174	2020-04-01 17:52:34.000	2020-04-01 17:53:18.040	000000001582	Bim Gold Pin	1	10	10	1
    031A482E-6C02-419D-B8A1-0F145DA2F079	98184487	3	9027765	2020-04-01 14:23:00.000	2020-04-01 14:24:23.733	000000001573	Bim 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 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 Steam Bakiyesi	1	10	10	1
    36B30CCD-B843-44C1-AEA2-283625D4ECD0	98184487	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	96113761	1	150578	2020-04-01 17:44:55.000	2020-04-01 17:44:55.787	000000001570	10 Steam Bakiyesi	1	10	10	1
    F9143D45-40BF-4471-8579-391947BA1D7D	97014860	2	9026698	2020-04-01 16:19:24.000	2020-04-01 16:19:19.427	000000001570	10 Steam Bakiyesi	1	10	10	1
    D992C1FC-4CC5-42D1-8176-3D1C89018EF2	97014860	2	9026698	2020-04-01 16:17:51.000	2020-04-01 16:17:46.613	000000001570	10 Steam Bakiyesi	1	10	10	1
    8B52A941-4F5E-41BE-A9EB-4059BF09F794	96171615	1	133109	2020-04-01 16:51:25.957	2020-04-01 16:51:37.163	000000001585	2100 ZA	1	5	5	1

    Saturday, April 4, 2020 3:44 PM

All replies

  • User475983607 posted

    The most practical approach is creating a new column that contains the two characters.  Otherwise use standard SQL commands like SUNSTRING() to get characters.   The official docs cover the details.

    https://docs.microsoft.com/en-us/sql/t-sql/functions/substring-transact-sql?view=sql-server-ver15

    Saturday, April 4, 2020 4:28 PM
  • User-1104215994 posted

    I don't understand the benefit of creating a new column, could you please elaborate?

    Saturday, April 4, 2020 4:31 PM
  • User475983607 posted

    I don't understand the benefit of creating a new column, could you please elaborate?

    Think about it... if you had a separate column with the two characters then GROUP and SUM becomes very easy.  Since the characters are embedded in ShopNo, you have to extract the characters into a new column so you can GROUP and SUM.

    Saturday, April 4, 2020 4:43 PM
  • User-1104215994 posted

    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);
                }
            }
        }

    Sunday, April 5, 2020 5:52 PM
  • User475983607 posted

    This is how I interpreted your original requirement where GameRequest is the original data shown.

      SELECT 
           SUBSTRING([shopno],3,2) ShopNo
          ,SUM([unitprice]),
          ,SUM([totalprice])
      FROM [dbo].[GameRequest]
      GROUP BY SUBSTRING([shopno],3,2)

    Sunday, April 5, 2020 7:52 PM