S-SQL Examples G

S-SQL Examples Home Page

A B C D E F G H I J K L M N O P R S T U V W Special Characters Calling Postgresql Stored Functions and Procedures

Generate-Series

(query (:select 'x (:generate-series 0 'x)
                           :from (:as (:values (:set 0) (:set 1) (:set 2))
                                      (:t 'x))))

'((0 0) (1 0) (1 1) (2 0) (2 1) (2 2))

Group-By

The following two examples use group-by. The first determines the number of countries in my table in each region and returns the list in region name order. The second determines the country with the maximum latitude for each region. 

(query (:order-by
        (:select 'regions.name
                 (:count 'regions.name)
                 :from 'countries 'regions
         :where (:= 'regions.id 'countries.region-id)
         :group-by 'regions.name)
        'regions.name))

(("Africa" 38) ("Asia" 27) ("Caribbean" 15) ("Central America" 6)("Central Asia" 5) ("Eastern Europe" 11)
               ("Middle East" 13) ("North America" 5)("Pacific" 24) ("South America" 14)
               ("Western Europe" 39))

(query (:select 'regions.name (:max 'latitude)
                :from 'countries 'regions
        :where (:= 'regions.id 'region-id)
        :group-by 'regions.name))

(("Pacific" 378/25) ("Western Europe" 65) ("Asia" 46) ("Central Asia" 48)("Caribbean" 483/20) ("Eastern Europe" 60)
                    ("North America" 72) ("Middle East" 39)("Central America" 343/20) ("Africa" 34) ("South America" 15))