SQL : hiding a calculated column-CodePudding

CREATE table orders 
{
    id integer,
    product_id integer,
    type VARCHAR(16)
}

SELECT
    (SELECT COUNT(*) FROM orders) AS "Order Count",
    -- I don't want total to show up
    (SELECT COUNT(*) FROM orders WHERE product_id = 500) AS "total",
    (SELECT COUNT(*) FROM orders WHERE product_id = 500 AND type = 'small') * 100 / "total" AS "% Small Sold",
    (SELECT COUNT(*) FROM orders WHERE product_id = 500 AND type = 'medium') * 100 / "total" AS "% Medium Sold",
    (SELECT COUNT(*) FROM orders WHERE product_id = 500 AND type = 'large') * 100 / "total" AS "% Large Sold"
FROM 
    orders

I have this SQL report. I have a number of columns and one of I'm creating to use to calculate my other columns, in this case "total". I don't want it to appear in the report though. Is there a way to code it in some other part of the query or mark it as hidden? I'm using Postgres.

CodePudding user response：

You can use a common table expression (CTE) for your totals. Then select the fields you want to keep in your report from the CTE.

WITH totals AS (
SELECT
  (SELECT COUNT(*) FROM orders) AS "Order Count",
  -- I don't want total to show up
  (SELECT COUNT(*) FROM orders WHERE product_id = 500) AS "total",
  (SELECT COUNT(*) FROM orders WHERE product_id = 500 AND type = 'small') * 100 / "total" AS "% Small Sold",
  (SELECT COUNT(*) FROM orders WHERE product_id = 500 AND type = 'medium') * 100 / "total" AS "% Medium Sold",
  (SELECT COUNT(*) FROM orders WHERE product_id = 500 AND type = 'large') * 100 / "total" AS "% Large Sold"
FROM orders    
)
SELECT
  "Order Count",
  "% Small Sold",
  "% Medium Sold",
  "% Large Sold"
FROM
totals

CodePudding user response：

I'm not sure what you mean by hidden but I have to show you a better way to write this query

SELECT
  COUNT(*) AS "Order Count",
  -- I don't want total to show up
  SUM(CASE WHEN PRODUCT_ID = 500 THEN 1 ELSE 0 END) AS "total",
  SUM(CASE WHEN PRODUCT_ID = 500 AND type = 'small' THEN 1 ELSE 0 END) * 100 / SUM(CASE WHEN PRODUCT_ID = 500 THEN 1 ELSE 0 END) AS "% Small Soldl",
  SUM(CASE WHEN PRODUCT_ID = 500 AND type = 'medium' THEN 1 ELSE 0 END) * 100 / SUM(CASE WHEN PRODUCT_ID = 500 THEN 1 ELSE 0 END) AS "% Medium Soldl",
  SUM(CASE WHEN PRODUCT_ID = 500 AND type = 'large' THEN 1 ELSE 0 END) * 100 / SUM(CASE WHEN PRODUCT_ID = 500 THEN 1 ELSE 0 END) AS "% Large Soldl",
FROM orders

I expect you will see a significant increase in performance

CodePudding user response：

Why don' t you filter first? It is decreasing your query performance.

Check this:

with maintab as (select case
when type is not Null then type
else 'total'
end type, count(*) cnt from orders
    where product_id = 500 and type in ('small', 'medium', 'large')
    group by rollup(type))
    select type, cnt*100/(select cnt from maintab where type = 'total' ) percentage
    from maintab
    where type in ('small', 'medium', 'large');

You can compare explain plans.