聚合函数然后按查询分组 [英] Aggregate function and then group by query

问题描述

你好我有一个查询，它给我以下结果：

Hello I have a query which gives me the following result:

Company   FileCount      Type        Total
ABK        233           SEA         39610
ABK        452           AIR         63280
ABK        2683          GROUND      375620
OPL        675           SEA         114750
OPL        11            AIR         1540
OPL        460           GROUND      64400
ABR        139           SEA         23630 
ABR        5             AIR         700
ABR        80            GROUND      11200

我不知道它是否可能，或者它是否有任何意义，但我试图完成是：

重新计算按公司分组的总价值以及按类型分组后的数据。

所以我认为它会像

I do not know if it is possible or will it make any sence but what I try to accomplish is:
Recalculate the total value which is grouped by Company and after that group by Type.
So i think it will be something like

SELECT Company, FileCount, Type, 
  CASE
    WHEN Count(FileCount) <= 1000 AND T.Company = 'ABK' THEN Total*25/100
    WHEN Count(FileCount) > 1000 AND Count(FileCount) <= 2500 AND T.Company = 'ABK' THEN Total*35/100
    WHEN Count(FileCount) <= 1000 AND T.Company = 'OPL' THEN Total*25/100
    WHEN Count(FileCount) > 1000 AND Count(FileCount) <= 2500 AND T.Company = 'OPL' THEN Total*35/100
    WHEN Count(FileCount) <= 1000 AND T.Company = 'ABR' THEN Total*25/100
    WHEN Count(FileCount) > 1000 AND Count(FileCount) <= 2500 AND T.Company = 'ABR' THEN Total*35/100
  END AS PercTotal
FROM Temp
GROUP BY Type

可以有人流光。在此先感谢。

can someone shed a light. Thanks in advance.

推荐答案

您好，



检查以下代码...

Hi,

Check the below code...

DECLARE @Test TABLE (Company  VARCHAR(10), FileCount INT, Type VARCHAR(20), Total NUMERIC(10,0))
INSERT INTO @Test(Company,FileCount,Type,Total)
SELECT 'ABK',233,'SEA',39610
UNION ALL
SELECT 'ABK',452,'AIR',63280
UNION ALL
SELECT 'ABK',2683,'GROUND',375620
UNION ALL
SELECT 'OPL',675,'SEA',114750
UNION ALL
SELECT 'OPL',11,'AIR',1540
UNION ALL
SELECT 'OPL',460,'GROUND',64400
UNION ALL
SELECT 'ABR',139,'SEA', 23630 
UNION ALL
SELECT 'ABR',5,'AIR', 700
UNION ALL
SELECT 'ABR',80,'GROUND',11200


SELECT T.Company,
	   T.Type,
	   T.FileCount,
	   CASE WHEN T.FileCount <= 1000 THEN T.Total*(25.0/100)
	        WHEN T.FileCount > 1000 AND T.FileCount <=2500 THEN T.Total*(35.0/100)
	   ELSE T.Total END 'Total',
FROM (SELECT Company,Type, SUM(FileCount) 'FileCount',SUM(Total) 'Total'
	  FROM @Test
	  GROUP BY Company,Type) T

问候，

GVPrabu

Regards,
GVPrabu

这篇关于聚合函数然后按查询分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！