iframe-proxy | Sunbelt Computer Software

80 lines (60 loc) · 2.51 KB

-- We can use "AS" to relabel a field inside a particular query.
-- This not only makes the report look nicer by displaying a more useful
-- header name, but it makes it easier to reference a field elsewhere
-- in the query.
-- "invoices" table
-- A list of the top 5 countries by number of invoices
SELECT billing_country, COUNT(*) AS invoice_count
FROM invoices
GROUP BY billing_country
ORDER BY invoice_count DESC
-- A list of the top 8 countries by gross/total invoice size
SELECT billing_country, SUM(total) AS gross_sales
FROM invoices
GROUP BY billing_country
ORDER BY gross_sales DESC
-- A list of the top 10 countries by average invoice size
SELECT billing_country, AVG(total) AS avg_invoice_size
FROM invoices
GROUP BY billing_country
ORDER BY avg_invoice_size DESC
-- Sales volume and receipts by year
-- See: http://www.sqlite.org/lang_datefunc.html
-- "STRFTIME" means "string format time" and is used to format the output of
-- dates, times, and timestamps.
-- See: http://cl.ly/image/0C1m372r233t
SELECT STRFTIME('%Y', invoice_date) AS year,
       COUNT(*) AS invoice_count,
       SUM(total) AS invoice_total
FROM invoices
GROUP BY year
ORDER BY year ASC;
-- Sales volume and receipts by year and month
-- You can group by multiple fields
-- You can order by multiple fields (here, year first then month)
-- See: http://cl.ly/image/2e2H3w052H2o
SELECT STRFTIME('%Y', invoice_date) AS year,
       STRFTIME('%m', invoice_date) AS month,
       COUNT(*) AS invoice_count,
       SUM(total) AS invoice_total
FROM invoices
GROUP BY year, month
ORDER BY year ASC, month ASC;
-- A list of the top 5 US states by number of invoices
-- Hint: You'll need to filter the results with WHERE billing_country = 'USA'
-- A list of the top 5 US states by gross sales
-- A list of the top 5 US states by average invoice size
-- A list of the top 10 US cities by number of invoices
-- A list of the top 10 US cities by gross sales
-- A list of the top 10 US cities by average invoice size
-- A list of the top 3 cities in California by number of invoices
-- Hint: You'll need a WHERE clause filtering by both billing_country and billing_state
-- A list of the top 3 cities in California by gross sales
-- A list of the top 3 cities in California by average invoice size
-- "customers" table
-- Remember: run ".schema customers" to see what fields (columns) the customers table contains.
-- A list of the top 3 countries by total number of customers
-- A list of the top 7 cities (anywhere) by total number of customers

Sunbelt Computer Software

PL/B Language Development and Support

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Sunbelt Computer Software

PL/B Language Development and Support

Uh oh!

FilesExpand file tree

group_by.sql

Latest commit

History

group_by.sql

File metadata and controls