Creating a Query to Sum Grouped by Fields

Toad Data Point & Toad Intelligence Central
1

I'm a novice to TDA and would appreciate help creating a query in Query Builder.

The attached image required_output.bmp shows the result I would like. ie. Group By Certificate Number and then Pipe Diameter and then sum the Geometric Length for these values in a new field called Totals.

TDA_Query.bmp is the query I have created so far. I'm OK with grouping Certificate Number and Pipe Diameter, but don't know how to sum the lengths into a new field Total.

Thanks

Jane

vranger_5.jpeg
vranger_5.jpeg807×268 160 KB

2

What you should do: don’t make Geometric Length a group item by clicking in the Group By box. Uncheck the group box for Geometric Length, and instead pick “Sum” as the function from the drop-down in the Aggregate functions entry field. I created a sample table called PIPES with similar columns, and I constructed my query like the picture you see attached to this post.
qb_sum_example.png

3

Thanks, that is helpful. What about if I want to display other fields?

In the images shown here I have added another field called GID. The result I would like to see is the summed length for grouped by Certificate Number and Pipe Diameter. ie. For the two records with a Pipe Diameter of 375 in this example, the summed length for both records should be 9.8.

Can you assist me with this query? Regards Jane
results.bmp

4

Thanks, that is helpful. What about if I want to display other fields?

In the images shown here I have added another field called GID. The result I would like to see is the summed length for grouped by Certificate Number and Pipe Diameter. ie. For the two records with a Pipe Diameter of 375 in this example, the summed length for both records should be 9.8.

Can you assist me with this query? Regards Jane
criteria.bmp

5

You can add several columns as long as they are group by columns. I’m not sure exactly what kind of output you are looking for. Suppose your data was something like this:

Data

pipe_dia certificate_number gid geom_length
450 SC070058 5318848 2
450 SC070058 5318848 3
375 SC070058 5318853 1.1
375 SC070058 3273120 2.7
375 SC070059 3273120 4
375 SC070059 3273120 6

Would you like your query results to look like the query results I show below? (Grouping by pipe_dia, certificate_number, gid) If so, then the query you have built should return these results.

If not, could you tell me what the output should look like for the above dataset?

Query Results

pipe_dia certificate_number gid sum (geom_length)
450 SC070058 5318848 5
375 SC070058 5318853 1.1
375 SC070058 3273120 2.7
375 SC070059 3273120 10
sample_data.txt (680 Bytes)

6

Thanks. I really am only interested in grouping by the fields pipe_dia and certificate_number, although I would still like to return other fields (eg.gid).

So all the records from the original data will be returned, however a new field (geom_length) which sums the pipe geom_length (for grouped certificate_number and pipe_dia) will be added to each record.

So the query results I expect here are:

pipe_dia certificate_number gid sum(geom_length)
450 SCO70058 5318848 5
450 SCO70058 5318848 5
375 SCO70058 5318853 3.8
375 SCO70058 3273120 3.8
375 SCO70059 3273120 10
375 SCO70059 3273120 10

Thanks

7

I see. You want to have summary totals but also want to see the detail rows. There are several possible answers depending on the RDBMS.
What RDBMS are you using? Oracle, SQLServer, DB2, MySQL, something else?

8

Oracle 10g.

Thanks.

9

There are two solutions: the generic solution (will work in any RDBMS) and another solution that is more efficient and will work in Oracle.

Generic solution: use subqueries (this is something you can build in the Toad for Data Analysis Query Builder)

select
b.pipe_dia, b.certificate_number, d.gid, b.sum_geom_length
from
(select a.pipe_dia, a.certificate_number, sum (a.geom_length) as sum_geom_length
from pipes a
group by a.pipe_dia, a.certificate_number) b,
(select c.pipe_dia, c.certificate_number, c.gid from pipes c) d
where b.pipe_dia = d.pipe_dia and b.certificate_number = d.certificate_number
order by b.pipe_dia, b.certificate_number, d.gid ;

better solution, using advanced features (analytic functions) - this is not something you could build in the Toad for Data Analysis Query Builder, you would need to type the query in the editor:

select a.pipe_dia, a.certificate_number, a.gid,
sum (a.geom_length) over (partition by a.pipe_dia, a.certificate_number) as sum_geom_length
from
pipes a
order by a.pipe_dia, a.certificate_number, a.gid ;

You can find out more about Oracle analytic functions in the Oracle Data Warehousing guide.

10

Thanks!

11

You’re welcome. After taking a second look at my previous post I realized that my “generic” solution was more complicated than it needed to be, it could be rewritten in a simpler way as
select
a.pipe_dia, a.certificate_number, a.gid, c.sum_geom_length
from
pipes a,
(select b.pipe_dia, b.certificate_number, sum (b.geom_length) as sum_geom_length
from pipes b
group by b.pipe_dia, b.certificate_number) c
where a.pipe_dia = c.pipe_dia and a.certificate_number = c.certificate_number
order by a.pipe_dia, a.certificate_number, a.gid ;