Revenue Summary for a historical transactional sales table


#1

Say your original table is:

type | id | productcount | platformcount | revenue | profit | discount
---- | -- | ------------ | ------------- | ------- | ------ | -----------

You can convert that into a wide-horizontal table with summary profit/revenue-type statistics this way:

SELECT 
   DATE(timecreated) AS timecreated
   , COUNT(CASE WHEN sales_table.type = 'contest' THEN id ELSE 0 END) AS contest_orders
   , SUM(CASE WHEN sales_table.type = 'contest' THEN productcount ELSE 0 END) AS contest_productcount
   , SUM(CASE WHEN sales_table.type = 'contest' THEN platformcount ELSE 0 END) AS contest_platformcount
   , SUM(CASE WHEN sales_table.type = 'contest' THEN revenue ELSE 0 END) AS contest_revenue
   , SUM(CASE WHEN sales_table.type = 'contest' THEN profit ELSE 0 END) AS contest_profit
   , SUM(CASE WHEN sales_table.type = 'contest' THEN discount ELSE 0 END) AS contest_discount
   , COUNT(CASE WHEN sales_table.type = 'project' THEN id ELSE 0 END) AS project_orders
   , SUM(CASE WHEN sales_table.type = 'project' THEN productcount ELSE 0 END) AS project_productcount
   , SUM(CASE WHEN sales_table.type = 'project' THEN platformcount ELSE 0 END) AS project_platformcount
   , SUM(CASE WHEN sales_table.type = 'project' THEN revenue ELSE 0 END) AS project_revenue
   , SUM(CASE WHEN sales_table.type = 'project' THEN profit ELSE 0 END) AS project_profit
   , SUM(CASE WHEN sales_table.type = 'project' THEN discount ELSE 0 END) AS project_discount
   , COUNT(CASE WHEN sales_table.type = 'tasks' THEN id ELSE 0 END) AS task_orders
   , SUM(CASE WHEN sales_table.type = 'task' THEN productcount ELSE 0 END) AS task_productcount
   , SUM(CASE WHEN sales_table.type = 'task' THEN platformcount ELSE 0 END) AS task_platformcount
   , SUM(CASE WHEN sales_table.type = 'task' THEN revenue ELSE 0 END) AS task_revenue
   , SUM(CASE WHEN sales_table.type = 'task' THEN profit ELSE 0 END) AS task_profit
   , SUM(CASE WHEN sales_table.type = 'task' THEN discount ELSE 0 END) AS task_discount
   , COUNT(CASE WHEN sales_table.type = 'template' THEN id ELSE 0 END) AS template_orders
   , SUM(CASE WHEN sales_table.type = 'template' THEN productcount ELSE 0 END) AS template_productcount
   , SUM(CASE WHEN sales_table.type = 'tempalte' THEN platformcount ELSE 0 END) AS template_platformcount
   , SUM(CASE WHEN sales_table.type = 'template' THEN revenue ELSE 0 END) AS template_revenue
   , SUM(CASE WHEN sales_table.type = 'template' THEN profit ELSE 0 END) AS template_profit
   , SUM(CASE WHEN sales_table.type = 'template' THEN discount ELSE 0 END) AS template_discount
    FROM sales_table
    GROUP BY 1