Beefy

I am learning how to write SQL queries in SAP Busines One. One of the columns in my query is full of figures and I want the SQL query to total that column up at the bottom and give me the SUM.

Someone advised me to use a UNION statement, but having looked into that I cant see how that is going to give me a column total within the report as it seems to be used for joining two separate queries.

Is it possible to run query 1 to show the column with the values in and query 2 to total the values in the relevant column of query 1? Its a bit beyond my feeble abilities at the moment to work it out.

Can anyone help?

AlexJReid

No idea about SAP.

UNION is used to give you the values from the results of two queries with any duplicates are removed. Your total will be unique so this will probably work.

SELECT col FROM table;
UNION
SELECT SUM(col) FROM table;

Would be easier just to return two resultsets.

SELECT col FROM table;
SELECT SUM(col) FROM table;

You could also just do the sum aggregation outside of the database, i.e. while(rs.next()) { total += rs.getInt("col"); }

scoobz72

I use SAP R3. All our SQL can be managed from exporting to your local desktop. Are you using SAP on a server or on a PC?
If you are using SAP on a server, then maybe SAP helpdesk would possibly help further.

I take it you are using the data in Excel to use the UNION function.

J4CKO

iTrader: (1)

You are really talking about reporting or programming here, in SQL Server you can use the CASE statement, like this example

SQL Server: Calculating Running Totals, Subtotals and Grand Total Without a Cursor


In Oracle I would drop down to PL/SQL and use a cursor that goes round the records adding to some total variables, display the detail lines then output the total line.

You could perhaps create a view, another query or temporary table with the results of the sum and select the detail rows as the report body and then select from the totals view/query/temp table.

Theres also Pivot tables/crosstabs, Subqueries

This will do a running total.
SQL Running Totals

Dont know SAP but you tend to find that each dialect has some add ons for these situations so RTM.

GaryK

Doesnt SAP B1 use SQL 2005 as its datastore? You can use the union or the two query syntax as specified by Alex, what are you grouping on? Customers, orders?

Beefy

Thanks for all the replies and apologies for the delay in responding, we've had some hardware upgrades to perform this morning.

Gary - SAP B1 does indeed use SQL2005 for new adopters of the software but we have yet to make the move so are still using SQL2000 SP4 (we went live years ago with v6.5 of SBO).

I guess it wouldve helped if I posted my SQL query so you could all see where I was up to so it is below (if this looks odd its because it has been created using the SAP B1 Query Generator rather than Access or any other tool):

SELECT T0.DocNum, T0.DocDate, T0.CardCode, T0.CardName, T0.CreateDate, SUM(T1.LineTotal+T1.LineVat) AS GBP_Invoice_Total
FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry
GROUP BY T0.DocNum, T0.DocDate, T0.CardCode, T0.CardName, T0.CreateDate
ORDER BY T0.docnum

I had a problem in that anything I used in the SELECT statement had to be included in the GROUP BY statement (not sure if this is a limitation of SAP's query writer or a SQL rule).

Where i have the SUM part of the statement, it is the results that appear in this column of the report that I want to have a further total for at the bottom of the column.

AlexJReid

I'm not convinced that forcing the total into the initial record set is the best way to go.

Better calculated elsewhere, or as a separate result set.

SUM is an aggregate function, grouped on the (optional) GROUP BY clause.

Beefy

The problem I have is that the people running the report are not very good in Excel, and have to run this report regularly for printing. For the optimum time saving they want to run the report within SAP and press print then forget about it (so it includes the totals on the print).

AlexJReid

Something along the lines of (untested):

SELECT 'Line',col INTO #t FROM table;
DECLARE @total MONEY;
SELECT @total = SUM(col) FROM #t;
INSERT INTO #t VALUES('Total',@total);
SELECT * FROM #t;
DROP TABLE #t;

would return:

Line 50.00
Line 50.00
Line 10.00
Total 110.00

Stil not an advisable solution. Maybe some SAP people will be able to advise!

Dracoro

Use the following:
CUBE
ROLLUP
or you could use COMPUTE for the whole total if not aggregating.

GaryK

You could try;

SELECT T0.DocNum, T0.DocDate, T0.CardCode, T0.CardName, T0.CreateDate, SUM(T1.LineTotal+T1.LineVat) AS GBP_Invoice_Total,
T2.grandtotal
FROM ORIN T0 INNER JOIN
RIN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN (select sum(t1.LineTotal+t1.LineVat) as grandtotal from ORIN) AS T2 on t0.DocEntry = t2.DocEntry
GROUP BY T0.DocNum, T0.DocDate, T0.CardCode, T0.CardName, T0.CreateDate
ORDER BY T0.docnum

Yes if a column is included in a select then you must include it in the group by, unless you do a sub-select like the example above which can let you mix aggregated and non-aggregated queries into 1 statement.

Gary

Beefy

Thanks Gary I will give that a try in the morning.