sql order by with group by rollup [英] sql order by with group by rollup

问题描述

我的查询如下所示.输出

电流输出

角色案例已准备完成 %成员国 1 10 5 50%成员国 2 10 7 70%州总统 10 2 20%总结 30 14 46.6%

预期输出

角色案例已准备完成 %州总统 10 2 20%成员国 1 10 5 50%成员国 2 10 7 70%总结 30 14 46%

角色表

id 名称30 州总统40 州议员

这是我的查询，

SELECT COALESCE(ROLE, 'Summary') ROLE,计数(*)AS案例，总和(准备时的情况 = 'Y' THEN 1其他 0完)如准备，round(avg(case when prepare = 'Y' then 100 else 0 end),2)||'%' as % Completed从(选择案例当 r.id = 30 THEN r.name其他 r.name ||' ' ||u.case_member_id作为角色结束，双向准备FROM 案例 c左连接 case_inventory ci ON ci.case_id = c.idAND c.id = ci.case_id并且 c.delete_date 为 NULLAND ci.case_id =40Left JOIN users u ON ci.assigned_to = u.id左加入角色 r ON u.role_id = r.id左 JOIN user_cases_map uc ON c.id = uc.case_id和 uc.id = 1572919346)按汇总分组(角色)；

我现在想根据角色对行进行排序.第一个记录应该是州总统，然后是州议员 1. 州议员 2. 等等.我试图在内部子句中有一个命令，但它没有帮助.它没有任何效果.添加外部选择也不会改变任何东西.任何帮助高度赞赏.谢谢.

解决方案

从你当前的查询开始，你可以使用 avg():

SELECTCOALESCE(ROLE, 'Summary') 角色，COUNT(*) AS 案例，SUM(CASE WHEN PREPARED = 'Y' THEN 1 ELSE 0 END) 已准备好，AVG(CASE WHEN PREPARED = 'Y' THEN 1 ELSE 0 END) AS ratio_completed从 ...

这将返回 0 和 1 之间的数字.如果您喜欢百分比(介于 0 和 100 之间)，您可以将 THEN 1 替换为 THEN 100.>

My query looks like below with the foll. output

Current Output

Role            Cases prepped % Completed    
State Member 1  10    5       50%
State Member 2  10    7       70%
State President 10    2       20%
Summary         30    14      46.6%

Output Expected

Role            Cases prepped % Completed
State President 10    2       20%
State Member 1  10    5       50%
State Member 2  10    7       70%
Summary         30    14      46%

Roles table

id name
30 State President
40 State Member

This is my query,

SELECT COALESCE(ROLE, 'Summary') ROLE,   
       count(*) AS cases, 
       SUM(CASE WHEN PREPARED = 'Y' THEN 1
                ELSE 0
           END) AS prepped, 
 round(avg(case when prepared = 'Y' then 100 else 0 end),2)||'%' as % Completed
FROM
  (SELECT CASE
              WHEN r.id = 30 THEN r.name
              ELSE r.name || ' ' || u.case_member_id
          END AS ROLE,
          bi.prepared
   FROM cases c
   LEFT JOIN case_inventory ci ON ci.case_id = c.id
   AND c.id = ci.case_id
   AND c.delete_date IS NULL
   AND ci.case_id =40
  Left JOIN users u ON ci.assigned_to = u.id
   Left JOIN ROLES r ON u.role_id = r.id
   Left JOIN user_cases_map uc ON c.id = uc.case_id
   AND uc.id = 1572919346)
GROUP BY ROLLUP (ROLE);

I now want to order the rows with respect to the role. The 1st record should be the State president and then followed by state memebr 1. state member 2. and so on. I tried to have an order by in the inner clause but it did not help. It doesnt have any effect. Adding in the outer select also doesnt change anything. Any help highly appreciated. Thank you.

解决方案

Starting from your current query, you can just use avg():

SELECT 
    COALESCE(ROLE, 'Summary') ROLE,   
    COUNT(*) AS cases, 
    SUM(CASE WHEN PREPARED = 'Y' THEN 1 ELSE 0 END) AS prepped, 
    AVG(CASE WHEN PREPARED = 'Y' THEN 1 ELSE 0 END) AS ratio_completed
FROM ...

This returns a numeric between 0 and 1. You can replace THEN 1 with THEN 100 if you prefer a percentage (between 0 and 100).

这篇关于sql order by with group by rollup的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！