Highlighted

qoq summary rows

Advisor ,
Jan 26, 2016

Copy link to clipboard

Copied

Hi All,

I have a complex query putting multiple tables and views data using union all, such as:

<cfquery name="preQry" dbtype="query">

select 'groupA' as Grouping, Item as Desc, sum(fieldCalA) as fielTotal

from tableA

group by Grouping, Desc

union all

select 'groupB' as Grouping, item as Desc, sum(fieldCalA) as fielTotal

from viewB

group by Grouping, Desc

.....

</cfquery>

The data looks like:

GROUP, DESCRIPTION, TOTAL

----------------------------------------------

groupA, ItemA, 10

groupA, ItemB, 25

groupB, ItemC, 30

groupB, ItemD, 20

groupB, ItemE, 50

groupC, itemF, 5

....

How can I add a summary row in my qoq? so my data can be like:

DESCRIPTION, TOTAL

--------------------------------

groupA, 35

   ItemA,10

   ItemB, 25

groupB, 100

  ItemC, 30

  ItemD, 20

  ItemE, 50

groupC, 5

  ItemF, 5

....

Thanks in advance.

Views

122

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

qoq summary rows

Advisor ,
Jan 26, 2016

Copy link to clipboard

Copied

Hi All,

I have a complex query putting multiple tables and views data using union all, such as:

<cfquery name="preQry" dbtype="query">

select 'groupA' as Grouping, Item as Desc, sum(fieldCalA) as fielTotal

from tableA

group by Grouping, Desc

union all

select 'groupB' as Grouping, item as Desc, sum(fieldCalA) as fielTotal

from viewB

group by Grouping, Desc

.....

</cfquery>

The data looks like:

GROUP, DESCRIPTION, TOTAL

----------------------------------------------

groupA, ItemA, 10

groupA, ItemB, 25

groupB, ItemC, 30

groupB, ItemD, 20

groupB, ItemE, 50

groupC, itemF, 5

....

How can I add a summary row in my qoq? so my data can be like:

DESCRIPTION, TOTAL

--------------------------------

groupA, 35

   ItemA,10

   ItemB, 25

groupB, 100

  ItemC, 30

  ItemD, 20

  ItemE, 50

groupC, 5

  ItemF, 5

....

Thanks in advance.

Views

123

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jan 26, 2016 0

Have something to add?

Join the conversation