Subscribe to this thread
Home - General / All posts - SQL, syntax WITH ROLLUP
tonyw
406 post(s)
#10-Jan-18 23:43

I am learning SQL in M9 to get subtotal of areas (hectares), the subtotals are based on entries in a field in the Table, in this example, grouping is by [ColourThem]. The 3 lines below generate a table subtotalling area (ha) by ColourThem category. I have found reference in literature outside Manifold's M9 user manual to generate a grand total using "WITH ROLLUP". I have tried various variations of WITH ROLLUP putting it between Group and By, by putting it after GROUP BY [ColourThem] and by appending to it WITH ROLLUP [Hectares].

SELECT [ColourThem], Sum([Hectares]) AS Area_ha

FROM [My Areas of Interest Drawing Table]

GROUP BY [ColourThem]

I can generate subtotals using the above then re-write as below to generate a grand total. Can I do it in one step?

SELECT Sum([Hectares]) AS Area_ha

FROM [My Areas of Interest Drawing Table]

The message in the log is "Invalid object reference" as below whenever I try variations of incorporating WITH ROLLUP into the statements:

> SELECT [ColourThem], Sum([Hectares]) AS Area_ha

FROM [My Areas of Interest Drawing Table]

GROUP BY [ColourThem] WITH ROLLUP

Invalid object reference.

tjhb

7,601 post(s)
online
#11-Jan-18 05:04

WITH ROLLUP is not Manifold 9 syntax.

Wherever you got it from does not apply.

adamw


7,444 post(s)
#11-Jan-18 07:05

We do not have ROLLUP.

You can generate subtotals by repeating the query, eg, like this:

--SQL9

SELECT Count(*) AS c, [Name] FROM [mfd_meta] GROUP BY [Name]

  UNION ALL

SELECT Sum(c), '' FROM (

SELECT Count(*) AS c, [Name] FROM [mfd_meta] GROUP BY [Name]

);

...or, better, like this:

--SQL9

FUNCTION intermediate() TABLE AS (

  SELECT Count(*) AS c, [Name] FROM [mfd_meta] GROUP BY [Name]

END;

EXECUTE CALL intermediate()

  UNION ALL

SELECT Sum(c), '' FROM CALL intermediate();

ROLLUP would mostly do the same internally.

Dimitri


4,422 post(s)
#11-Jan-18 11:35

I am learning SQL in M9

Chris Fehily's books are great to learn SQL.

Also, SQL implementations routinely vary. To see if a command in a book you are reading is in 9, enter it into the filter box for commands in the Command Window. For example, if you enter ROLLUP and nothing appears, you know there is no command named that in 9.

tonyw
406 post(s)
#11-Jan-18 22:37

Thanks Tim, Adam and Dimitri for the tips.

The filter box works well to narrow down to the required syntax. The attached screen shot shows my SQL and results of subtotals and grand total. From previous learning, I could probably add RoundDecs(<>,2) to round to 2 decimal places.

An observation, are queries saved with the .map file in M9? I added queries (e.g., the ones to generate the results in the attached screen shot), then I closed the .map file but was not asked if I wanted to save changes. Checking if there was auto-save, I re-opened my project file but the queries were not saved. This behaviour can be repeated. I created the same queries yesterday and when I opened the project file today, the queries were not there.

Attachments:
Subtotals and Grand total by SQL.JPG

tjhb

7,601 post(s)
online
#12-Jan-18 00:13

are queries saved with the .map file in M9?

Query components are saved, yes, but queries written in a Command window are not.

Code in a Command window (for SQL, IronPython, IronRuby, JavaScript, later maybe C#) is volatile. Once the Command window is closed, its context and content disappear.

To create a non-volative query, do exactly what you would do in Manifold 8, i.e. Create > New Query. Code in a query component is normally safe.

(There are some situations where it's not. For example, try making some edits to a query, then, with the query still open, rename it in the Project pane. Puf, the query closes and the edits are gone. You currently have to close the query manually or run it to make the edits stick.)

Dimitri


4,422 post(s)
#12-Jan-18 09:04

You can also save queries written in a Command window to a query component using Edit - Save as Query.

I use that all the time after I've been hacking away at the Command window for a while and then I realize "hmm... maybe this is worth saving..."

lionel
249 post(s)
#12-Jan-18 00:04

ROLLUP syntax is not cover by Chris Fehily Book SQL (Database Programming) (2015 Edition)

regard's color with rgb not red is suported in georeference editor

Dimitri


4,422 post(s)
#12-Jan-18 09:11

ROLLUP syntax is not cover by Chris Fehily

Yes... a good example of why I recommend Fehily books on SQL. He does a good job of discussing SQL in a way that is applicable to most SQL engines, and when he touches on something that is not broadly applicable he calls it out. If you learn SQL from a Fehily book the learning usually is more directly applicable to 9.

tonyw
406 post(s)
#12-Jan-18 17:16

Thanks again for the tips re: saving my queries, also I'll check out Chris Fehily's books. Chris has several that cover beginner SQL.

tonyw
406 post(s)
#13-Jan-18 08:00

I emailed Chris Fehily to ask which of his several SQL books I should start with, if it's OK to post the title here, he recommended "SQL (Database Programming)", current edition is 2015.

Dimitri


4,422 post(s)
#15-Jan-18 07:02

It's very cool Fehily responded personally. I wrote him a while back but never got a reply - probably lost in spam filtration. We'd love to slide him a free copy of Manifold. :-)

tonyw
406 post(s)
#15-Jan-18 18:47

I will convey offer to Chris.

lionel
249 post(s)
#16-Jan-18 03:44

so there is 2 differents books of Fehily

-SQL: Visual QuickStart Guide (3rd Edition): Chris Fehily

-SQL (Database Programming) Paperback – July 14, 2014

the download page on manifold website have been update

  • books2.map (6528 KB) - A Release 9 format .map file providing the Books example database used in Chris Fehily's "SQL Database Programming (2015 Edition)," another fine Fehily book on SQL.
  • books.map (6592 KB) - A Release 9 format .map file providing the Books example database used in Chris Fehily's first-rate book, "SQL: Visual QuickStart Guide," a great book for learning SQL rapidly.

Attachments:
Fehily_books.png

tjhb

7,601 post(s)
online
#16-Jan-18 04:01

You can’t go wrong. I have both (in fact, five editions).

Get a paper version, so you can write. (No point in being lonely!)

The absolutely best edition is the one you have read most closely, and have already made most notes in.

Nothing will ever compare to the edition that makes you go “Aha!”.

Dimitri


4,422 post(s)
#16-Jan-18 07:19

I love the automotive oil filter on the cover... :-)

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.