Clever Geek Handbook

Cartesian Products - SUM two columns in one table

I am trying to use SUM two columns in one table with different conditions for SUM. The problem I'm running into is trying to get the results in two different columns. If I use this query, I get a 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, 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.

+3


source to share


2 answers


you can use CASE

for 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


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
+2


source


You can use subquery



select distinct sum(p1.arc_baseEventCount)  AS 'Total Logger Event Count'
, (select sum(p2.arc_deviceCustomNumber3) 
from events p2 on p1.something = p2.something
where p2.arc_name = 'Connector Raw Event Statistics') as 'Total Connector Event Count'

from events p1
0


source






More articles:


All Articles