Trouble in showing SUM() value of a column

Question

0.00/5 (No votes)

See more:

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:

SQL

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

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:

SQL

| 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:

SQL

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

but I get this:

SQL

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

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.

Posted 9-Aug-13 16:32pm

MyOldAccount

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MyOldAccount · Accepted Answer · 2013-08-09T18:21:00

Solution 1

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

SQL

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');

Posted 9-Aug-13 18:21pm

MyOldAccount

Updated 9-Aug-13 18:24pm

v2

Comments

Maciej Los 10-Aug-13 9:38am

Interesting example!
+5!
See my answer if you want to see alternative ways ;)

Maciej Los · Accepted Answer · 2013-08-10T03:17:00

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

SQL

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) using CASE[^] statement:

SQL

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 ;)

Gauri Chodanker · Accepted Answer · 2013-08-11T19:25:00

Solution 3

Another solution for the same result

SQL

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

Posted 11-Aug-13 19:25pm

Gauri Chodanker

Comments

MyOldAccount 12-Aug-13 9:29am

Thank you!