question

michael112 avatar image
michael112 asked

Using SUM and SUBSTRINT in same statement

Hi All, I have a table called Table1 Col1 Col2 Col3 Col4 ABC T:\Data1 10 1 ABC T:\Data2 20 2 ABC T:\Data3 30 3 ABC T:\Data4 40 4 ABC T:\Data5 50 5 I want the results to be Col1 Col2 Col3 Col4 ABC T: 150 15 I want to get one line results I tried using SUBSTRING for Col2 and SUM for Col3 and Col4 But was getting errors Please Help
sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
after you have applied the substring you need to either aggregate the value, or group on it select col1, substring(col2,1,2), sum(col3), sum(col4) from table1 group by col1, substring(col2,1,2) or select col1, max(substring(col2,1,2)), sum(col3), sum(col4) from table1 group by col1
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Both the queries I tried were exactly the same, I'm still getting error msg saying "is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
0 Likes 0 ·
I'm not able to achieve one line results
0 Likes 0 ·
out of curiosity, what are the results when you run this query: create table #table1 (Col1 varchar(30), Col2 varchar(80), Col3 int, Col4 int) insert #table1 values ('ABC', 'T:\Data1', 10, 1), ('ABC', 'T:\Data2', 20, 2), ('ABC', 'T:\Data3', 30, 3), ('ABC', 'T:\Data4', 40, 4), ('ABC', 'T:\Data5', 50, 5) select col1, substring(col2,1,2), sum(col3), sum(col4) from #table1 group by col1, substring(col2,1,2) drop table #table1 Using Kev's query, I see this: ----- ---- ----- ----- ABC T: 150 15
0 Likes 0 ·
What is the full error message? Which column is SQL complaining about?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.