CODEWITHSUNDEEP
sqlsql-server
tkr
tkr

Reputation: 25

SQL Server : group rows with similar column values to one row and then sum the corresponding

I am writing a query to get the columns with same starting letter grouped into one and the next column with the sum of its respective row values. Example as below. Have tried case and sum but not working. Is this possible.

Col1  |Col2  |Col3
------+------+------
axxx  |1     |z
axyx  |2     |p
bxxy  |1     |q
bxyx  |2     |y
cxxy  |1     |t
cxyx  |2     |t

The query should be returning as below

FirstLetter |Total(Sum of Col2)
------------+------------------
a           |3
b           |3
c           |3

Where the first column is the string which starts with the same first letter Second column gives the sum of all the values from col2 with the Col1 value starting with the same first letter

Is this possible?

Upvotes: 1

Views: 980

Answers (2)

Ramji
Ramji

Reputation: 385

By using substring https://www.w3schools.com/sql/func_sqlserver_substring.asp

create table #1 (col1 varchar(10), col3 varchar(10))

insert into #1 values ('axxx','1'),('axyz','2')

SELECT  substring(col1, 1, 1) AS col1_substring, sum(col3) AS col2count
FROM    #1
GROUP   BY substring(col1, 1, 1);

Upvotes: 1

Sean
Sean

Reputation: 1474

SQL has had a left function for a while now. 

select left(Col1, 1), sum(Col2)
  from Table_Name
 group by left(Col1, 1)

Upvotes: 5

Related Questions