How do you join two calculated sql statement?

Question

The statement below unions both queries, but leaves nulls for companies that do not have a calculated value. How can I remove nulls from these two columns? Or is there a better way to combine these two calculated columns into one table. Thanks.

SELECT Company,

       Sum(CASE
               WHEN voltage = '99 kV' THEN number * 1
               WHEN voltage = '199 kV' THEN number * 2
               WHEN voltage = '299 kV' THEN number * 3
               WHEN voltage = '399 kV' THEN number * 4
               WHEN voltage = '599 kV' THEN number * 5
               WHEN voltage = '799 kV' THEN number * 6
               ELSE 0
           END) AS 'score1',
       NULL AS score2
FROM Table1    
WHERE [year] = '2020'     
GROUP  BY Company    
UNION ALL    
SELECT Company,    
       NULL AS score1,
       Sum(CASE
               WHEN voltage = '99 kV' THEN number * 1
               WHEN voltage = '199 kV' THEN number * 2
               WHEN voltage = '299 kV' THEN number * 3
               WHEN voltage = '399 kV' THEN number * 4
               WHEN voltage = '599 kV' THEN number * 5
               WHEN voltage = '799 kV' THEN number * 6
               ELSE 0
           END) AS 'score2'
FROM Table2    
WHERE [year] = '2020'     
GROUP  BY Company    
ORDER BY Company

Try combining the 2 tables _before_ doing the aggregation. – Zack Jan 15 '21 at 19:55 — Zack, Jan 15 '21 at 19:55

score 0 · Answer 1 · answered Jan 15 '21 at 19:46

0

You can replace the NULL values with empty strings:

isnull(convert(varchar(36),col),'') as ...

This should probably do it.

answered Jan 15 '21 at 19:46

itsDV7

854
5
12

How would I get a distinct company with Score1 and score 2? instead of having the same company on 2 separate rows? – YCao Jan 15 '21 at 19:57

Gordon Linoff · Answer 2 · 2021-01-15T21:41:05.717

0

You can use join:

select t1.company, t1.score1, t2.score2
from (<query1>
     ) t1 join
     (<query2>
     ) t2
     on t1.company = t2.company;

This returns only companies in both result sets. You might want an outer join of some sort if you want companies that are missing from one of the result sets. If you use an outer join, you might need coalesce(t1.company, t2.company) in the select.

edited Jan 15 '21 at 21:41

answered Jan 15 '21 at 20:40

Gordon Linoff

1,242,037
58
646
786

I used a full outer join. There are calculated values in t1 but not in t2, and vise versa. So if the value is in t2, then the company column is null for that value. – YCao Jan 15 '21 at 20:53
@ycao use `SELECT COALESCE(t1.company, t2.company) AS company, ...` when using a `FULL OUTER JOIN`. That way, if one is NULL, then you'll use the other. – MatBailie Jan 15 '21 at 21:09

score 0 · Answer 3 · answered Jan 15 '21 at 21:01

I would choose the following method due to it's reduction in repeated code...

SELECT
  company,
  SUM(CASE WHEN source = 1 THEN score ELSE 0 END)   AS score1,
  SUM(CASE WHEN source = 2 THEN score ELSE 0 END)   AS score2
FROM
(
  SELECT
    company,
    [year],
    source,
    CASE
      WHEN voltage = '99 kV'  THEN number * 1
      WHEN voltage = '199 kV' THEN number * 2
      WHEN voltage = '299 kV' THEN number * 3
      WHEN voltage = '399 kV' THEN number * 4
      WHEN voltage = '599 kV' THEN number * 5
      WHEN voltage = '799 kV' THEN number * 6
      ELSE 0
    END
      AS score
  FROM
  (
    SELECT company, [year], 1 AS source, voltage, number FROM Table1
    UNION ALL
    SELECT company, [year], 2 AS source, voltage, number FROM Table2
  ) 
    AS combined
) 
  AS scored
WHERE
  [year] = 2020
GROUP BY
  company
ORDER BY
  company

It can be made neater by using Common Table Expressions rather than sub queries, but you didn't specify which database and version you're using, so I didn't include that.

How do you join two calculated sql statement?

3 Answers3