finding world gdp formula (SUM OVER)

PHOTO

Thu Apr 21 2022 04:35:36 GMT+0000 (Coordinated Universal Time)

Saved by @Bambibo9799

Since a GROUP BY is included, global_gdp will require two SUM() functions.

-- Pull country_gdp by region and country
SELECT 
	region,
    country,
	SUM(gdp) AS country_gdp,
    -- Calculate the global gdp
    SUM(SUM(gdp)) OVER () AS global_gdp,
    -- Calculate percent of global gdp
    SUM(gdp) / SUM(SUM(gdp)) OVER () AS perc_global_gdp,
    -- Calculate percent of gdp relative to its region
    SUM(gdp) / SUM(SUM(gdp)) OVER (PARTITION BY region) AS perc_region_gdp


-- Bring in region, country, and gdp_per_million
SELECT 
    region,
    country,
    SUM(gdp) / SUM(pop_in_millions) AS gdp_per_million,
    -- Output the worlds gdp_per_million
    SUM(SUM(gdp)) OVER () / SUM(SUM(pop_in_millions)) OVER () AS gdp_per_million_total,
    -- Build the performance_index in the 3 lines below
    (SUM(gdp) / SUM(pop_in_millions))
    /
    (SUM(SUM(gdp)) OVER () / SUM(SUM(pop_in_millions)) OVER ()) AS performance_index

Save snippets that work from anywhere online with our extensions

Available in the Chrome Web Store

Get Firefox Add-on

Get VS Code extension

Comments

More like this

SQL

@Bambibo9799

Delete Duplication SQL Using Subquery to find salary above average Sorting Rank Number In Sql find nth Salary in SQL sql - Counting rows in the table which have 1 or more missing values - Stack Overflow How to create customized quarter based on a given date column sql sql server - Stack Overflow get total sales per quarter, every year delete duplicate for table without primary key SQL Server REPLACE Function By Practical Examples adding row_number row_id to table find total sales per cateogry each year change empty string into null values Case when to update table update table using replace function How to Use CASE WHEN With SUM() in SQL | LearnSQL.com find each year sale in each city and state, then sum them all using regexp_replace to remove special chracter postgresql find quarter sale in each state, and sum it using unbounded preceding, and using percent rank create delimiter for the phone number regexp_replace to remove special characters using row between to cummulative sum update null values using WHERE filter insert into table command sum cummulative value in SQL SQL Important syntax Auto Increment in SQL read json file on postgresql Moving Average n Moving Total pivot on potgresql Rollup and Cube date function in postgresql select specify part of string using substring change column data type exclude null values in sql how to exclude zero in our counting finding outlier in sql how to import data with identifier (primary key) email and name filtering in sql trimming and removing particular string from sql replace string on sql regexp to find string in SQL answers only about email, identifier, lower, substring, date_part, to_char find percentage of null values in total remove duplicate on both tables v2 any and in operator string function moreee bucket function find perfomance index in sql find top max and top min finding world gdp formula (SUM OVER) find month percentage change find highest height in sql with row_number and subquery JOIN AND UNION offset and limit in sql function and variables using views on postgresql find cummulative sums in postgresql find null and not null percentage find specific strings or number in SQL using case when and CAST function Lpad function in Postgresql and string function in general Regexp function in postgresql regular expressions example in postgresql And FUZZYSTRMATCH updated method of deleting duplicates determine column types

Browse more snippets >>