topic How to write SQL query and apply aggregate functions on it in Application Development and Automation Discussions

How to write SQL query and apply aggregate functions on it

former_member295881 — Tue, 13 Jul 2010 02:15:29 GMT

Hello experts,

Iu2019ve a task to write SQL query on tree tables and do inner join on them. Iu2019ve accomplish this task by using T-CODE SQVI. However now I need to write a query and apply SQL functions on it i.e. Add, Count, Max and Min etc. Please can someone tell me how I can write SQL query with aggregate functions in SAP?

Thanks a lot in advance

Re: How to write SQL query and apply aggregate functions on it

Former Member — Tue, 13 Jul 2010 02:30:53 GMT

SQVI limits in what can be done with the SQL Queries. Use SQ02 to first create infosets, it also provides you with options to write custom code blocks and then you can create queries in SQ01 based on the infoset created in SQ02.

Refer this article on [working with Queries|http://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/40bec8da-4cd8-2910-27a9-81f5ce10676c].

Re: How to write SQL query and apply aggregate functions on it

Former Member — Tue, 13 Jul 2010 04:16:49 GMT

Hi,

Select using an aggregate function

DATA: MAX_MSGNR type t100-msgnr.

SELECT MAX( MSGNR ) FROM T100 INTO max_msgnr

WHERE SPRSL = 'D' AND

ARBGB = '00'.

Re: How to write SQL query and apply aggregate functions on it

Former Member — Tue, 13 Jul 2010 05:03:46 GMT

HI Mr. Cool

you can see the below code for using aggregate functions.

where ARTIST and SEATSOCCU are the field names for which you want to perform these functions.

DATA: TOTAL_ENTRIES TYPE I,

TOTAL_ATT TYPE I,

MAX_ATT TYPE I,

AVG_ATT TYPE I.

SELECT COUNT( DISTINCT ARTIST )

SUM( SEATSOCCU )

MAX( SEATSOCCU )

AVG( SEATSOCCU ) FROM YCONCERT INTO (TOTAL_ENTRIES, TOTAL_ATT,

MAX_ATT, AVG_ATT).

Thanks

Lalit Gupta