Geek Answers Handbook

How to add aggregate value in SQL

Now my data table is like

uid mid  ..
1   xx   ..
2   xx   ..
-   a    ..
-   b    ..
...

I wrote two SQL queries:

select count(distinct uid) from T where uid!='-'and select count(distinc mid) from T where uid='-',

Now I want to get the sum of these two values โ€‹โ€‹in only one SQL query. Anyone have an idea to help me?

+4
source share
2 answers

You can do it as follows:

SELECT (SELECT count(DISTINCT uid) FROM T WHERE uid <> '-') +
       (SELECT count(DISTINCT mid) FROM T WHERE uid = '-') As sumOfTwo

or

;WITH t1 AS (
    SELECT count(DISTINCT uid) cnt FROM T WHERE uid <> '-'
), t2 AS (
    SELECT count(DISTINCT mid) cnt FROM T WHERE uid = '-'
)
SELECT t1.cnt + t2.cnt As sumOfTwo
FROM t1 CROSS JOIN t2

or [Recommended]

SELECT count(DISTINCT CASE WHEN [uid] <> '-' THEN [uid] END) +
       count(DISTINCT CASE WHEN [uid] = '-' THEN [mid] END)
FROM T
+2
source

try this query

select count(distinct uid) from T where uid!='-'
union all
select count(distinc mid) from T where uid='-'
+2
source

More articles:


All Articles