无法显示列的SUM()值 [英] Trouble in showing SUM() value of a column

问题描述

您好，我来自CodeProject的同事们！

我会简短地讲到重点:

我使用C ++在Windows XP上使用ADO访问MS Access 2007数据库.

我在MS Access 2007中有一张表，其中包含2个标题为TypeOfObject和InstaledPower的感兴趣的列.

实际表如下所示:

Hello my fellow colleagues from CodeProject!

I will be brief, and cut to the point:

I work on Windows XP, in C++, using ADO to access an MS Access 2007 database.

I have a table in MS Access 2007 which contains 2 columns of interest titled as TypeOfObject and InstaledPower.

The actual table looks something like this:

| TypeOfObject | InstaledPower |
-------------------------------
|     Type1    |     1000      |
-------------------------------
|     Type2    |     2000      |
-------------------------------
|     Type3    |      450      |
-------------------------------
|     Type4    |      800      |
-------------------------------
|     Type1    |      800      |
-------------------------------

我需要一个查询，以这种方式显示TypeOfObject和该类型的sum(InstaledPower):

如果TypeOfObject的值为Type1或Type2，则在表中显示其值不变，否则将其标记为OtherTypes.结果应该是这样的:

I need a query that displays TypeOfObject and sum( InstaledPower ) for that type, in this manner:

If value of TypeOfObject is Type1 or Type2, than show it''s value in table unchanged, else mark it as OtherTypes. The result should be like this:

| TypeOfObject | SUM(InstaledPower) |
-------------------------------------
|     Type1    |     1800           |  <--- 1000 + 800 = 1800
-------------------------------------
|     Type2    |     2000           |  <--- only one Type2, so it is 2000
-------------------------------------
|   OtherTypes |     1250           |  <--- sum( InstaledPower ) of Type3 and Type4
------------------------------------

我已尝试使用以下查询:

I have tried with this query:

SELECT TypeOfObject ,
  SUM( InstaledPower  ) as [SUM(InstaledPower)]
  FROM MyTable
  group by TypeOfObject  ;

但我明白了:

but I get this:

| TypeOfObject | SUM(InstaledPower) |
-------------------------------------
|     Type1    |     1800           | <--- correct
-------------------------------------
|     Type2    |     2000           |
-------------------------------------
|     Type3    |      450           | <--
-------------------------------------    |-- this isn't what I need, see above
|     Type4    |      800           | <--
-------------------------------------

通过Internet搜索，我得出的结论是，可以使用数据透视表解决此问题，但是我不知道如何正确使用它们.

如果真是这样，如果数据透视表可以解决此问题，那么将非常感谢一些链接.

如果我还有其他需要帮助的地方，请询问，我会很乐意这样做.

感谢所有尝试提供帮助的人.

By searching through Internet, I have come to the conclusion that this problem might be solved with pivot tables, but I do not know how to use them properly.

If that is the case, if pivot table can solve this, some links would be greatly appreciated.

If there is anything else that I can do to help, ask and I will gladly do it.

Thanks to everybody who tries to help.

推荐答案

在比我更好的开发人员和Internet的帮助下，我能够使用以下查询解决此问题:

With the help of better developers than me, and Internet I was able to solve it using this query:

SELECT 
IIF(TypeOfObject in('Type1', 'Type2'), TypeOfObject, 'OtherTypes') 
      as TypeOfObject , 
SUM( InstaledPower ) as [SUM(InstaledPower)] 
FROM MyTable
group by 
      IIF(TypeOfObject in('Type1', 'Type2'), TypeOfObject, 'OtherTypes');

替代方法:
1)使用 UNION [

Alternative ways:
1) using UNION[^] statement:

DECLARE @tmp TABLE (TypeOfObject VARCHAR(30), InstaledPower INT)

INSERT INTO @tmp (TypeOfObject, InstaledPower)
SELECT 'Type1', 1000
UNION ALL SELECT 'Type2', 2000
UNION ALL SELECT 'Type3', 450
UNION ALL SELECT 'Type4', 800
UNION ALL SELECT 'Type1', 800


SELECT *
FROM (
    SELECT TypeOfObject , SUM(InstaledPower) as SUMOfInstaledPower
    FROM @tmp
    WHERE TypeOfObject IN('Type1','Type2')
    GROUP BY TypeOfObject
    UNION ALL
    SELECT 'Other Types' TypeOfObject , SUM(InstaledPower) as SUMOfInstaledPower
    FROM @tmp
    WHERE TypeOfObject NOT IN('Type1','Type2')
) AS T

结果:

Result:

TypeOfObject	SUMOfInstaledPower
Type1		1800
Type2		2000
Other Types	1250

2)使用案例 [

2) using CASE[^] statement:

SELECT TypeOfObject, SUM(InstaledPower) as SUMOfInstaledPower
FROM (
	SELECT CASE
		WHEN TypeOfObject = 'Type3' THEN 'Other Types'
		WHEN TypeOfObject = 'Type4' THEN 'Other Types' 
		ELSE TypeOfObject END AS TypeOfObject, InstaledPower
	FROM @tmp
) AS T
GROUP BY TypeOfObject

结果与上述相同；)

Result is the same as above ;)

针对相同结果的另一种解决方案

Another solution for the same result

SELECT
CASE WHEN TYPEOFOBJECT NOT IN ('TYPE1','TYPE2') THEN 'OTHERS'
     ELSE TYPEOFOBJECT END TYPEOFOBJECT
, SUM(INSTALLEDPOWER) INSTALLEDPOWER
FROM T_POWER
GROUP BY CASE WHEN TYPEOFOBJECT NOT IN ('TYPE1','TYPE2') THEN 'OTHERS'
               ELSE TYPEOFOBJECT END

这篇关于无法显示列的SUM()值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！