question about reporting output in SQL

sfullman

Hi There,

I'm working on some functions which will generate reports from SQL queries automatically with values for headers and footers. For example, the query I might run would be:

"Select FirstName, LastName, Age from Table"

... so the result might be:

John     Smith     15
Joe       Law       25
Sue      Knight    32

mySQL or the database server is returning 3 rows and 3 columns. The question I have is, does SQL ONLY specify returns of this type? Or is there any language set that would return more so that we'd get a resource of:

FirstName   LastName  Age
John            Smith        15
Joe              Law          25
Sue             Knight       32
                                    82

Which is of course FIVE rows by 3 columns now (82 is the sum of the ages for example).

Was Structured Query Langauge ever remotely envisioned for this type of functionality? And if you have any URL links, I'd appreciate if you let me know.

Sincerely,
Sam Fullman
Compass Point Media

eoghain

No SQL was never intended to be a report generator. It is what it is a way to query a database for data. If you want your data to be nicely formatted you need to do it yourself, or get a reporting software like Crystal Reports that will allow you to build a report template and attach it to a query.

ultraslacker

SQL is based on relational algebra and deals with sets of data. Most dbms's have some degree of noncompliance with the SQL standard and will allow you to do such a thing as your report, it is poor practice to do so as it is external to the database.

Aggregates are supported and the aggregate that you would like is possible through a subquery. Of course, the formatting is not, but the presentation of data is external to a database. Presentation should be handled in the application.

C.J. Date and E.F. Codd are excellent resources for learning about database theory and the relational model. Here's a collection of links

tomhath

As is usually the case, the answer is "it depends".

As far as the column headings, you have them already if you fetch the rows as an associative array, so there's no need for a separate row.

You can get the column totals from some databases like MSSQL Server (and maybe Sybase, same thing) with the COMPUTE BY or ROLLUP operators:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_08_4ego.asp