Asked by:
How to sum based on a criteria

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