Query to sum 2 Columns

russel · #1 (**permalink**) 07-15-11, 06:28

SELECT Col1 AS A, Col2 AS B, Col3 AS C FROM Table;

Code:

A               B           C          
1               2           3
4               5           6
8               9           5

Need to modify the query to display a fourth column,D which is the sum of first three.

Code:

A               B           C          D
1               2           3          6
4               5           6          15
8               9           5          22

r937 · #2 (**permalink**) 07-15-11, 06:44

Code:

SELECT Col1 AS A
     , Col2 AS B
     , Col3 AS C 
     , Col1 + Col2 + Col3 AS D
  FROM daTable

russel · #3 (**permalink**) 07-15-11, 07:14

but for col1 i've used ----

Code:

"COUNT(CASE WHEN `contact_status` = 'AAA'
THEN 'whoopee' ELSE NULL END ) AS `A`"

The Query is

Code:

SELECT COUNT(
CASE WHEN `status` = 'AAA'
THEN 'whoopee'
ELSE NULL
END ) AS `A`,
COUNT(
CASE WHEN `status` = 'BBB'
THEN 'whoopee'
ELSE NULL
END ) AS `B`,
COUNT(
CASE WHEN `status` = 'CCC'
THEN 'whoopee'
ELSE NULL
END ) AS `C`,

FROM `Table`

From this i want to display a fourth column,D

r937 · #4 (**permalink**) 07-15-11, 07:23

wow, that looks awfully familiar

1. why didn't you show the whole query the first time?

2. why can't you apply my solution to your query?

3. are AAA, BBB, CCC the only values in the status column, or are there others?

russel · #5 (**permalink**) 07-15-11, 07:31

I tried many possibilities with SUM(), but didn't get through.

I've only three values in that column

r937 · #6 (**permalink**) 07-15-11, 07:38

Quote:

Originally Posted by russel

I've only three values in that column

<voice actor="mr. burns">ehhhhhhhxcellent</voice>

Code:

SELECT AgentName 
     , COUNT(CASE WHEN status = 'RPC'
                  THEN 'whoopee'
                  ELSE NULL END) AS `Count of RPC`
     , COUNT(CASE WHEN status = 'BUS'
                  THEN 'whoopee'
                  ELSE NULL END) AS `Count of BUS`
     , COUNT(CASE WHEN status = 'NPC'
                  THEN 'whoopee'
                  ELSE NULL END) AS `Count of NPC`
     , COUNT(*) AS `Count of all three`
  FROM CustomerInfo
GROUP
    BY AgentName

russel · #7 (**permalink**) 07-15-11, 09:10

what if i've to display count of two more values from another column of the same table i.e;

Code:

A               B           C         D        E         F           G  
1               2           3         5        8         6           14
4               5           6         8        7         15         15
8               9           5         3        9         22         12

where F=A+B+C
AND G=D+E

and D and E belongs to count of values from another column of the same table

r937 · #8 (**permalink**) 07-15-11, 09:14

Quote:

Originally Posted by russel

what if ...

what if you abandon your As and Bs and Cs, and try writing the query yourself -- i've given you lots of information about the technique

post your attempt here if it doesn't work, but i'm not going to go back to As and Bs and Cs as i did in post #2 which turned out to be counter-productive

Internet Services

Web Development

Digital Marketing

Consumer Tech