Code:food2.sql: Difference between revisions

← Older edit

Code:food2.sql (view source)

Revision as of 17:58, 14 March 2023

948 bytes added , 14 March 2023

no edit summary

VisualWikitext

Elie

Bureaucrats, Interface administrators, Administrators

2,956

edits

@@ Line 6: / Line 6: @@
 * [[:File:fao-livestock-primary.csv]]
+==Code==
 <syntaxhighlight lang="sql">
--- Analysis of the production of animal products
+-- Analysis of the production of animal products --
+---------------------------------------------------
@@ Line 21: / Line 22: @@
 .mode list
+--## Animal products, worldwide production per capita
 CREATE TABLE summary AS
@@ Line 44: / Line 49: @@
--- Total animal protein and kalories
+-- Total animal protein produced:
 SELECT SUM(protein_grams_per_day_per_capita) FROM summary;
 SELECT SUM(kalories_per_day_per_capita) FROM summary;
+-- Meat production from all ruminants:
+SELECT SUM(food_grams_per_day_per_capita),
+    SUM(protein_grams_per_day_per_capita) FROM summary
+ WHERE Item="Meat, buffalo"
+    OR Item="Meat, cattle"
+    OR Item="Meat, goat"
+    OR Item="Meat, sheep";
+-- Milk production from all livestock species:
+SELECT SUM(food_grams_per_day_per_capita)*0.9776 AS milk_mL_per_day_per_capita, -- Milk is slightly denser than water. To convert "grams" to "milliliters milk", multiply by 97.76% (according to GNU Units)
+    SUM(protein_grams_per_day_per_capita) FROM summary WHERE Item LIKE "Milk%";
@@ Line 57: / Line 79: @@
 SELECT COUNT(DISTINCT `Item`) FROM animal;
 SELECT COUNT(DISTINCT `Item`) FROM animal WHERE `Area`="World" AND `Element`="Production" AND `Unit`="tonnes";
+-- Test that "World" is the only aggregate
+SELECT SUM(`Value`) FROM animal WHERE `Area` ="World" AND `Element`="Production" AND `Unit`="tonnes";
+SELECT SUM(`Value`) FROM animal WHERE `Area`<>"World" AND `Element`="Production" AND `Unit`="tonnes";
 </syntaxhighlight>