[Solved] Query to display rows and their sum

[shimonl]

Hi,
I am sending out invitations to my daughter's wedding, and trying to use Base to
keep track of the responses. (I already have tables, which I used for *sending* the invitations).

I am trying to figure out how to create an SQL query to do what seems to me a very simple operation:
To list all the records of a table, PLUS a sum of specific columns at the end. In this case:

Lastname Firstname Number_invited Number_RSVP


I can get the list of responses:
SELECT "Lastname", "Firstname", "Invited", "RSVP" FROM "WeddingDB"

And I can get the totals by themselves:
SELECT SUM( "Invited" ) AS "Invited", SUM( "RSVP" ) AS "RSVP" FROM "WeddingDB"

But what I wanted was both in one query, with output like this:

Code: Select all

Lastname  Firstname  Invited RSVP
Miller    George     2       0
Smith     John       2       2
Tailor    Robert     4       3
Walker    Henry      3       1

SUM:                 11      6

I would appreciate any help!
Thank you,
Shimon

[Sliderule]

Per your description, and, thank-you very much, for including both, your table names and column names. That makes providing the solution so much easier.

Please follow the steps below, to write the Query.

1. Open your database file ( *.odb )
2. On the left, click on the Queries icon
3. Under Tasks, click on: Create Query in SQL View...
4. Copy and paste the following Query into the panel that will be opened

   Code: Select all

   SELECT 
      "Lastname", 
      "Firstname", 
      "Invited", 
      "RSVP" 
   FROM "WeddingDB" 
      UNION 
   SELECT 
      'SUM:',
      '',
      SUM( "Invited" ), 
      SUM( "RSVP" ) 
   FROM "WeddingDB"
   

5. Critical step: Either:
   1. From the Toolbar, click on the SQL icon with a green check mark
   2. From the Menu: Edit -> Run SQL command directly should be checked, and, you can toggle it
6. Run your Query ( F5 )
7. Review the output
8. Save the Query
9. Save the Base file ( *.odb ) since you added a new Query
10. Smile and say: "Gee Sliderule, that was easy. Now all I have to do is let the forum know the everything worked, and, mark this as [Solved]" .

Explanation: Just to explain, what you want is a Query that uses the UNION command. That is, to run two Queries, and, include the results of both the first, and, the second Query together. Therefore, for each column in the first Query, there must be a similar column in the second. That is, the number of columns must be the same, AND, the column types ( VARCHAR, INTEGER, etc ) must match. That is the reason, for created a 'dummy' second column in the second Query. Furthermore, since the OpenOffice / LibreOffice Base Parser does not support the use of UNION but the database back-end ( HSQL ) does, you can send the Query directly to the database, SANS the Base Parser.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[shimonl]

"Gee Sliderule, that was easy. Now all I have to do is let the forum know that everything worked, and, mark this as [Solved]" .

Well, almost, but GREAT!!

The quirk I noticed was that SUM seemed to be missing, until I found it (him???) stuck in between Silver and Taylor.
So, just for the fun of it, I changed "SUM:" to "*SUM:", and got the totals at the TOP of the output.

Maybe what I needed was something like "APPEND" (if there even is such a thing), rather than UNION. UNION seems to take the
additional data and make it part of one big heap of information to display, which caused it to be put in its alphabetically ordered position.

In any case, it has solved my problem and I thank you!!
Shimon

[Sliderule]

Shimon:

The quirk I noticed was that SUM seemed to be missing, until I found it (him???) stuck in between Silver and Taylor.
So, just for the fun of it, I changed "SUM:" to "*SUM:", and got the totals at the TOP of the output.

Problem: What was happening, since, I suppose your table column name "Lastname" was a part of the Primary Key ( I did not know that ), the query as originally written by me, was returning the result set in that sorted order. Therefore, as you described, the text value of 'SUM:' was returned accordingly.

Solution: Simply made the result of the query for the calculated values to return text string 'ZZZZZSUM:' and for the final ( at the top ) displayed the result set, using REPLACE function to replace all 'ZZZZZ' string value with a zero length string.

Code: Select all

Select 
   REPLACE(A."Lastname", 'ZZZZZ', '') as "Lastname",  -- Use Replace to get rid of FAKE 'ZZZZZ' 
   A."Firstname",
   A."Invited",
   A."RSVP"
FROM 
(SELECT 
   "Lastname", 
   "Firstname", 
   "Invited", 
   "RSVP" 
FROM "WeddingDB" 
   UNION  -- Query uses UNION syntax, in OpenOffice / LibreOffice, must run Query directly, withOUT Parser
SELECT 
    'ZZZZZSUM:',   -- FAKE string and use REPLACE above to get rid of five Z's
   '',
   SUM( "Invited" ), 
   SUM( "RSVP" ) 
FROM "WeddingDB") as A

Thanks for already marking this as [Solved]. Also, hoping everyone has a joyous wedding.

Sliderule