ADDING FIELDS WITH A SELECT STATEMENT

Question

Post reply

ADDING FIELDS WITH A SELECT STATEMENT

sahoong

SSCrazy

Points: 2545
More actions
August 20, 2018 at 11:14 am

#367387

Can someone kindly help me with the query below:
SELECT 1 AS ID, '1 Field, COALESCE(SUM(Field1)/1000,'0.0') AS Field
FROM Table
WHERE Condition='Money' and SecondCondition = 'KRN'
UNION
SELECT 2 AS ID, '2Field, COALESCE(SUM(Field2)/1000,'0.0') AS Field
FROM Table
WHERE Condition='Money' and SecondCondition = 'LIR'
UNION
SELECT 3 AS ID, '3Field3, COALESCE(SUM(Field3)/1000,'0.0') AS Field
FROM Table
WHERE Condition='Money' and SecondCondition = 'EUR'
UNION
SELECT 4 AS ID, 'Addition of Field column in Row1, Row2, Row3' AS Total
I want to add the Field columns on Row1, Row2 and Row3 and name is Total on Row 4. What is the best way to accomplish this?
Thank you.
Sincerely,
Sahoong.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

With > 2000 points, you should know to provide DDL, sample data and desired results.

You should also know that they are called 'columns', not 'fields'.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

sahoong SSCrazy Points: 2545 More actions · Answer 2

Hi Phi,

I am trying to write the code. I only have a spreadsheet. I haven't got any DDL to send. Please, find attached the sample report. I want to add the amount for Row 1, Row 2 and Row 3 and put the sum in Sub Total column. And add all the sub total column together as Total. Thank you

Phil Parkin SSC Guru Points: 246986 More actions · Answer 3

sahoong - Monday, August 20, 2018 11:41 AM
Hi Phi,
I am trying to write the code. I only have a spreadsheet. I haven't got any DDL to send. Please, find attached the sample report. I want to add the amount for Row 1, Row 2 and Row 3 and put the sum in Sub Total column. And add all the sub total column together as Total. Thank you

So you want someone else to write all of the set-up code for you, because you cannot be bothered? No thanks.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

sahoong - Monday, August 20, 2018 11:41 AM
Hi Phi,
I am trying to write the code. I only have a spreadsheet. I haven't got any DDL to send. Please, find attached the sample report. I want to add the amount for Row 1, Row 2 and Row 3 and put the sum in Sub Total column. And add all the sub total column together as Total. Thank you

Then do it in the spreadsheet.

sahoong SSCrazy Points: 2545 More actions · Answer 5

its in spreadsheet and I am developing an SSRS Report for it.

drew.allen SSC Guru Points: 76997 More actions · Answer 6

sahoong - Monday, August 20, 2018 12:03 PM
its in spreadsheet and I am developing an SSRS Report for it.

If this is for an SSRS Report, you should be doing it in SSRS, not in T-SQL. SSRS was specifically designed to handle these kinds of situations.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

sahoong SSCrazy Points: 2545 More actions · Answer 7

SELECT 1 AS ID, '1 Cash on Hand - Local Currency' AS ROW_CATEGORY, COALESCE(SUM(GLAmount)/1000,'0.0') AS GLAmount
FROM v_GL
WHERE GLBSAttribute7 ='1. Cash' and GLCurrency ='SAR'

UNION

SELECT 2 AS ID, '2 Checks Under Collection - Local Currency' AS ROW_CATEGORY, COALESCE(SUM(GLAmount)/1000,'0.0') AS GLAmount
FROM v_GL
WHERE GLBSAttribute7 ='3 Items In Course of Collection' and GLCurrency ='SAR'

UNION

SELECT 3 AS ID, '3 Checks Under Collection - Foreign Currency' AS ROW_CATEGORY, COALESCE(SUM(GLAmount)/1000,'0.0') AS GLAmount
FROM v_GL
WHERE GLBSAttribute7 ='3 Items In Course of Collection' and GLCurrency <> 'SAR'

UNION

SELECT 4 AS ID, '4 Sub-Total' AS ROW_CATEGORY, SUM(GLAmount)

UNION

SELECT 5 AS ID, '5 Govt/Quasi Govt. Deposits - Local Currency' AS ROW_CATEGORY, COALESCE(SUM(GLAmount)/1000,'0.0') AS GLAmount
FROM v_GL
WHERE GLBSAttribute8 ='21.1 Demand' and GLCurrency = 'SAR'

UNION

SELECT 6 AS ID, '6 Govt/Quasi Govt. Deposits - Foreign Currency' AS ROW_CATEGORY, COALESCE(SUM(GLAmount)/1000,'0.0') AS GLAmount
FROM v_GL
WHERE GLBSAttribute8 ='21.1 Demand' and GLCurrency <> 'SAR'

UNION

SELECT 7 AS ID, '7 Private Sector - Local Currency' AS ROW_CATEGORY, COALESCE(SUM(GLAmount)/1000,'0.0') AS GLAmount
FROM v_GL
WHERE GLBSAttribute8 ='21.1 Demand' and GLCurrency = 'SAR'

UNION

SELECT 8 AS ID, '8 Private Sector - Foreign Currency' AS ROW_CATEGORY, COALESCE(SUM(GLAmount)/1000,'0.0') AS GLAmount
FROM v_GL
WHERE GLBSAttribute8 ='21.1 Demand' and GLCurrency <> 'SAR'

UNION

SELECT 9 AS ID, '9 Sub-Total' AS ROW_CATEGORY, SUM(GLAmount)

UNION

SELECT 10 AS ID, '10 Govt/Quasi Govt. Deposits - Local Currency' AS ROW_CATEGORY, COALESCE(SUM(GLAmount)/1000,'0.0') AS GLAmount
FROM v_GL
WHERE GLBSAttribute8 ='21.2 Time' and GLCurrency = 'SAR'

UNION

SELECT 11 AS ID, '11 Govt/Quasi Govt. Deposits - Foreign Currency' AS ROW_CATEGORY, COALESCE(SUM(GLAmount)/1000,'0.0') AS GLAmount
FROM v_GL
WHERE GLBSAttribute8 ='21.2 Time' and GLCurrency <> 'SAR'

UNION

SELECT 12 AS ID, '12 Private Sector - Time Deposits in Local Currency' AS ROW_CATEGORY, COALESCE(SUM(GLAmount)/1000,'0.0') AS GLAmount
FROM v_GL
WHERE GLBSAttribute8 ='22.3 Time' and GLCurrency = 'SAR'

UNION

SELECT 13 AS ID, '13 Private Sector - Time Deposits in Foreign Currencies' AS ROW_CATEGORY, COALESCE(SUM(GLAmount)/1000,'0.0') AS GLAmount
FROM v_GL
WHERE GLBSAttribute8 ='22.3 Time' and GLCurrency <> 'SAR'

UNION

SELECT 14 AS ID, '14 Private Sector - Saving Deposits in Local Currency' AS ROW_CATEGORY, COALESCE(SUM(GLAmount)/1000,'0.0') AS GLAmount
FROM v_GL
WHERE GLBSAttribute8 ='22.2 Savings' and GLCurrency = 'SAR'

UNION

SELECT 15 AS ID, '15 Private Sector - Saving Deposits in Foreign Currencies' AS ROW_CATEGORY, COALESCE(SUM(GLAmount)/1000,'0.0') AS GLAmount
FROM v_GL
WHERE GLBSAttribute8 ='22.2 Savings' and GLCurrency <> 'SAR'

UNION

SELECT 16 AS ID, '16 Sub-Total' AS ROW_CATEGORY, SUM(GLAmount)

UNION

SELECT 17 AS ID, '17 Cash Margins For Documentary Credits & Acceptances in local and Frgn Curr.' AS ROW_CATEGORY, COALESCE(SUM(GLAmount)/1000,'0.0') AS GLAmount
FROM v_GL
WHERE GLBSAttribute8 ='23.1 On Locs & Acceptances'

UNION

SELECT 18 AS ID, '18 Cash Margins For Guarantees in local and Frgn Curr.' AS ROW_CATEGORY, COALESCE(SUM(GLAmount)/1000,'0.0') AS GLAmount
FROM v_GL
WHERE GLBSAttribute8 ='23.2 On Guarantees'

UNION

SELECT 19 AS ID, '19 Other Cash Margins in Local & Foreign Currencies' AS ROW_CATEGORY, COALESCE(SUM(GLAmount)/1000,'0.0') AS GLAmount
FROM v_GL
WHERE GLBSAttribute8 ='22.3 Time'

UNION

SELECT 20 AS ID, '20 Outstanding Transfers - in Local Currency' AS ROW_CATEGORY, COALESCE(SUM(GLAmount)/1000,'0.0') AS GLAmount
FROM v_GL
WHERE GLBSAttribute8 ='22.3 Time' and GLCurrency <> 'SAR'

UNION

SELECT 21 AS ID, '21 Outstanding Transfers - in Foreign Currency' AS ROW_CATEGORY, COALESCE(SUM(GLAmount)/1000,'0.0') AS GLAmount
FROM v_GL
WHERE GLBSAttribute8 ='22.2 Savings' and GLCurrency = 'SAR'

UNION

SELECT 22 AS ID, '22 Sub-Total' AS ROW_CATEGORY, SUM(GLAmount)

UNION

SELECT 23 AS ID, '23 Total' AS ROW_CATEGORY, SUM(GLAmount)

sahoong SSCrazy Points: 2545 More actions · Answer 8

I need help with the subtotal rows. I want to add the GLAMount from previous row AS Sub-Total and Total on the last row is the addition of all sub-total.

Thank you.

drew.allen SSC Guru Points: 76997 More actions · Answer 9

sahoong - Monday, August 20, 2018 12:43 PM
I need help with the subtotal rows. I want to add the GLAMount from previous row AS Sub-Total and Total on the last row is the addition of all sub-total.
Thank you.

You should know by now that queries that reference tables/views (v_GL) that we DO NOT HAVE ACCESS TO, is not consumable data.

Also your code is highly inefficient. You are doing multiple reads on the same table when you should be using a CASE expression.

Finally, you already said that you were using SSRS. This kind of processing is extremely easy in a matrix report. Why are you still trying to do this in T-SQL?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

funbi SSCarpal Tunnel Points: 4992 More actions · Answer 10

funbi

SSCarpal Tunnel

Points: 4992

August 21, 2018 at 1:43 am

#2002623

Have a look at GROUP BY ... WITH ROLLUP

Chris Wooding SSCarpal Tunnel Points: 4559 More actions · Answer 11

What use is a total for amounts that are in different currencies anyway?

aalphasoftwaresolutions Newbie Points: 9 More actions · Answer 12

A SELECT STATEMENT WITH SQL:-

1.From the Tools menu, click SQLTerm Open the SQLTerm window button.
2.In the box at the top of the SQLTerm window, click the database with which you want to work.
3.Use the Cognos SQL check box to specify whether you want to use the native SQL or Cognos® SQL for the database you are accessing when you construct the SQL statement. Select or clear the check box as appropriate.
4.In the Database Objects pane, expand the tree, and then click the table or column for which you want to add a SELECT statement.
5.To generate the SQL statement use one of the following methods:- Right-click, and then click either Add Table Select Statement, or Add Column Select Statement.
-Hold down Ctrl and drag the table or column you selected in the Database Objects pane to the Query pane.
You are now ready to execute the SQL statement.

drew.allen SSC Guru Points: 76997 More actions · Answer 13

aalphasoftwaresolutions - Thursday, August 23, 2018 5:40 AM
A SELECT STATEMENT WITH SQL:-
1.From the Tools menu, click SQLTerm Open the SQLTerm window button.
2.In the box at the top of the SQLTerm window, click the database with which you want to work.
3.Use the Cognos SQL check box to specify whether you want to use the native SQL or CognosÂ® SQL for the database you are accessing when you construct the SQL statement. Select or clear the check box as appropriate.
4.In the Database Objects pane, expand the tree, and then click the table or column for which you want to add a SELECT statement.
5.To generate the SQL statement use one of the following methods:- Right-click, and then click either Add Table Select Statement, or Add Column Select Statement.
-Hold down Ctrl and drag the table or column you selected in the Database Objects pane to the Query pane.
You are now ready to execute the SQL statement.

I don't see anywhere that the OP states that he is using Cognos.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA