[comp.databases] Ingres report writer & aggregates/groups

robf@squid.rtech.com (Robert Fair) (03/27/89)

A recent poster was having  problems  doing aggregates with the INGRES
report writer (sum, count etc). Its actually very easy to do things like
sums/percentages over groups, for example to print the total salary
for each department, just do:

	.FOOT dept
		.printline "Total salary for dept is", sum(salary)

(the RW does all the mess of resetting the value at the break etc)
To print running totals  just use the CUM (cumulative) keyword, i.e:

	.FOOT dept
		.printline "Total salary for dept is", sum(salary)
		.printline "Total salary so far is", cum sum(salary)

If you need to do something like print the percentage of the total
value for each group, mix QUEL & RW commands:

	.QUERY
		retrieve (total=sum(emp.salary),emp.salary,emp.dept)
	...
	.FOOT dept
		.printline "Percentage of total salary used by dept",
			(sum(salary)/total)*100.0

(For SQL users its a little more complex, since SQL isn't as flexible with
 aggregates  as QUEL:
 In TM:
	CREATE VIEW  emptot (total) AS SELECT sum(salary) from emp;

 In RW:
	.QUERY
		select emptot.total,emp.salary,emp.dept
		from   emptot,emp;
)

If you need regular variables, this was  added to  the RW in INGRES Version 6,
along with keywords .LET & .DECLARE. Variables  are tightly-typed, include
NULL handling, and an optional PROMPT clause can be added to the .DECLARE
to get an initial value from the user at runtime.

Robert L. Fair
RTI Tech Support

[Disclaimer: This article is posted as an individual, not as a
representative of RTI.]