cartesian product - SUM two columns in the same table

Question

I am trying to SUM two columns in the same table with different conditions on the SUMs. The problem I am running into is trying to get the results into two different columns. If I use this query I get the cartesian product.

SELECT SUM(p1.arc_baseEventCount)  AS 'Total Logger Event Count',
       SUM(p2.arc_deviceCustomNumber3) AS 'Total Connector Event Count'
FROM EVENTS AS p1, EVENTS AS p2
WHERE p2.arc_name = "Connector Raw Event Statistics"

When I run this query I get the expected result but the result is in two rows and not two columns.

SELECT SUM(arc_baseEventCount) 'total event count'
FROM Events
UNION ALL
SELECT SUM(arc_deviceCustomNumber3)
FROM EVENTS
WHERE arc_name = "Connector Raw Event Statistics"

I know I am close but I am definitely missing something.

Answer 1

you can use CASE on this,

SELECT  SUM(arc_baseEventCount) 'total event count', 
        SUM(CASE WHEN arc_name = 'Connector Raw Event Statistics' THEN arc_baseEventCount ELSE NULL END) 'Connector Raw Event Statistics'
FROM    Events

• SQLFiddle Demo

UPDATE 1

SELECT  SUM(arc_baseEventCount) 'total event count', 
        SUM(CASE WHEN arc_name = 'Connector Raw Event Statistics' THEN arc_baseEventCount ELSE NULL END) 'total_1',
        SUM(CASE WHEN name = 'Connector Raw Event Statistics' THEN arc_deviceCustomNumber3 ELSE NULL END) 'total_2'
FROM    Events