0

I have table with more than 34 columns.

I need to aggregate(sum) of the columns.

To find:

  1. Sum of 2 columns
  2. Sum of remaining columns except.

Please find the table structure with following columns.

S.NO              
Handling charge
Storage Charge
...
..
.... 34th column charge

I have more than 3000 rows of records based on above mentioned columns.

May I know how to query or calculate above mentioned find.

For Find 1 Query:

**Update dbo.tablename
set columnname isnull(charge1,0)+isnull(charge2,0)**

I am struggling to find find 2:

Can anyone help?

krishna31
  • 113
  • 1
  • 9
  • add more details of your table – Vijunav Vastivch Oct 11 '18 at 05:28
  • probably same as this one ? https://stackoverflow.com/questions/52727267/sql-operation-on-the-column-sum-of-a-table/52727804#52727804 – iSR5 Oct 11 '18 at 05:30
  • Without mentioning sum(col1+col2+col3+...) is there any query sumallcolumns(except 2 columns) because charges name keep on changing. – krishna31 Oct 11 '18 at 05:38
  • 1
    There is no way something like you want. If you want to write a dynamic query, you need to extract the table's column from "sys.columns" in "master" database and create a query string and execute it using "execute()" function. I do not suggest you this way. – mohabbati Oct 11 '18 at 06:11

1 Answers1

0

You can query the table to get the list of columns name then apply the Sum() function.

Query table column names:

SELECT *
FROM DatabaseName.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'tableName'
G.Nader
  • 847
  • 7
  • 9
  • I tried with this query, but it returns null. Query I tried select * from bireports.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = N'fcl'. It reurns nothing. – krishna31 Oct 11 '18 at 07:58
  • So I used below Following query to list the column names,SELECT * FROM [Powerbireports].INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FCL_Pivot' and column_name like '%Charges'. Hereafter how can I perform sum and exclude two columns? – krishna31 Oct 11 '18 at 08:55
  • You have to exclude the two columns in the where condition ex: where column_name<>'Excluded_Columns_Name' – G.Nader Oct 11 '18 at 12:09