时间戳

首页 > 解决方案 > 具有连接和不同输出的 SQL 聚合函数

sql - 具有连接和不同输出的 SQL 聚合函数

问题描述

列出所拥有公司的名称、授权股份数量、已发行股份总数和已发行股份占授权股份的百分比。按公司名称对输出进行排序。

  SELECT c.name,
         sa.authorized,
         ROUND(css.total_outstanding/css.current_authorized * 100, 2)  AS "Outstanding"
    FROM company c JOIN 
         shares_authorized sa ON sa.stock_id = c.stock_id JOIN 
         current_stock_stats css ON css.stock_id = sa.stock_id
ORDER BY c.name;

在此处输入图像描述

附件是输出。我需要七行 7nrows 作为答案，而不是 8 行。纽约时间应该合并。使用 SUM 会返回 Group By 错误。我们还能在这里使用什么？

标签： sql

解决方案

SELECT name, authorized, ROUND(total_outstanding / current_authorized * 100, 2) AS "Outstanding"
FROM (
    SELECT c.name
        ,sa.authorized
        ,SUM(css.total_outstanding) AS total_outstanding
        ,SUM(css.current_authorized) AS current_authorized
    FROM company c
    JOIN shares_authorized sa ON sa.stock_id = c.stock_id
    JOIN current_stock_stats css ON css.stock_id = sa.stock_id
    GROUP BY c.name, sa.authorized
) TMP
ORDER BY name, authorized

推荐阅读