CODEWITHSUNDEEP
sqlsql-server
Sateesh Kumar
Sateesh Kumar

Reputation: 1

how to merge multiple distinct queries to a single query to see output in multiple rows

Right now i have multiple distinct queries which gives respective results .. But i wanted to run a single query to get all those outputs in multiple rows..

Ex:

select count(distinct message_id)
from dssam.message_metadata
where object_id > 1177 AND workflow_type='3';   --- This gives o/p - 24

select count(distinct message_id)
from dssam.message_metadata
where object_id > 1177 AND workflow_type='4'; --- This gives o/p - 40

select count(distinct message_id)
from dssam.message_metadata
where object_id > 1177 AND flagged='true';--- This gives o/p - 6

But what i am looking for is .. o/p should be as below:

 [workflow_type  count
==============   ====
3                 24
4                 40
true               6][1]

Can somebody help me on this ..?

Upvotes: 0

Views: 58

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Put the values in columns:

select count(distinct case when workflow_type = 3 then message_id end) as cnt_wt_3,
       count(distinct case when workflow_type = 4 then message_id end) as cnt_wt_4,
       count(distinct case when flag = 'true' then message_id end) as cnt_true      
from dssam.message_metadata m
where object_id > 1177 and
      ( workflow_type in (3, 4) or flag = 'true' );

(Note: I assume that workflow_type is really an integer.)

You want them in separate columns so you can tell which row corresponds to which value. Otherwise, you have no label on the row. In a table called message_metadata, I am guessing that message_id is unique.

If this is the case, do not use count(distinct). I prefer sum() for this calculation:

select sum(case when workflow_type = 3 then 1 else 0 end) as cnt_wt_3,
       count(case when workflow_type = 4 then message_id else 0 end) as cnt_wt_4,
       count(case when flag = 'true' then 1 else 0 end) as cnt_true      
from dssam.message_metadata m
where object_id > 1177 and
      ( workflow_type in (3, 4) or flag = 'true' );

Upvotes: 0

Serg
Serg

Reputation: 22811

A bit shorter version of single scan + unpivot query. 

WITH Aggs AS(
    SELECT COUNT(DISTINCT CASE mm.workflow_type WHEN 3 THEN message_id END) AS Workflow3,
           COUNT(DISTINCT CASE mm.workflow_type WHEN 4 THEN message_id END) AS Workflow4,
           COUNT(DISTINCT CASE mm.flagged WHEN 'true' THEN message_id END) AS FlaggedTrue
    FROM dssam.message_metadata mm
    WHERE [object_id] = 1177)
SELECT V.KPI, V.DistinctCount
FROM Aggs A
CROSS APPLY (VALUES('3', Workflow3),
                   ('4', Workflow4),
                   ('true', FlaggedTrue)
            ) V(KPI,DistinctCount);

Upvotes: 0

Thom A
Thom A

Reputation: 95949

You can avoid 3 scans of the table with some conditional aggregation:

WITH Aggs AS(
    SELECT COUNT(DISTINCT CASE mm.workflow_type WHEN 3 THEN message_id END) AS Workflow3,
           COUNT(DISTINCT CASE mm.workflow_type WHEN 4 THEN message_id END) AS Workflow4,
           COUNT(DISTINCT CASE mm.flagged WHEN 'true' THEN message_id END) AS FlaggedTrue
    FROM dssam.message_metadata mm
    WHERE [object_id] = 1177)
SELECT V.KPI,
       CASE V.KPI WHEN 'Workflow 3' THEN A.Workflow3
                  WHEN 'Workflow 4' THEN A.Workflow4
                  WHEN 'Flagged True' THEN A.FlaggedTrue
       END AS DistinctCount
FROM Aggs A
     CROSS APPLY (VALUES('Workflow 3'),
                        ('Workflow 4'),
                        ('Flagged True')) V(KPI);

Upvotes: 0

sticky bit
sticky bit

Reputation: 37487

You can use UNION ALL and a literal for the workflow_type column.

SELECT '3' workflow_type,
       count(DISTINCT message_id) count
       FROM dssam.message_metadata
       WHERE object_id > 1177
             AND workflow_type = '3'
UNION ALL
SELECT '4' workflow_type,
       count(DISTINCT message_id) count
       FROM dssam.message_metadata
       WHERE object_id > 1177
             AND workflow_type = '4'
UNION ALL
SELECT 'true' workflow_type,
       count(DISTINCT message_id) count
       FROM dssam.message_metadata
       WHERE object_id > 1177
             AND flagged = 'true';

Upvotes: 1

Related Questions