Groupby Aggregate and Inner queries in service now

[servicenowuser2]

Hello,i want to find a query similar to --- Select A.x,A.y   ,B.g,B.h,C.x,C.countx, (countx/B.g)*(A.x) ,BB.owner,BB.vertical from (select x,y from table A join   (select g,h,sum(columnx1)   from table B1 group by g,h) B join   (select x,sum(columnx2) as countx from C1 group by x) C   ) AA join select owner,vertical from application BB on AA.application= BB.application -- in service now.All tables are in service now and i want to use inner queries and join them and perform calculation on it.Can the query i just pasted be executed in service now.I want to leverage the "group by" feature that sql provides in service now.

[eican]

You can create a database view and define your inner queries in there.

Alternatively you can also perform a direct MySQL query but that would return the data as text/string

[servicenowuser2]

Hi Eican

Thanks you.

I want to do group by based on multiple fields to calculate sum based on different 2 fields.

Can i do group by and sum operations so that i get multiple rows in the result just like i get in sql?

Thankyou

[eican]

Probably you have to create the database view and then use GlideAggregate to get the SUM you require.

[servicenowuser2]

Can i do 2 grouping in one query itself?

Select A.x,A.y   ,B.g,B.h,C.x,C.countx, (countx/B.g)*(A.x) ,BB.owner,BB.vertical from

(select x,y from table A join  

        (select g,h,sum(columnx1)   from table B1 group by g,h) B

join  

(select x,sum(columnx2) as countx from C1 group by x) C   ) AA join select owner,vertical from application BB on AA.application= BB.application