How to Merge 3 Select into One

Asked

Viewed 200 times

0

I wonder how I could make one SELECT where I can bring the same resulting however in a single SELECT.

Example of the select I need to be single:

SELECT  COUNT(*) AS QtdTarifados
FROM    tblLoteTESTE
WHERE   CodCampanha         = 1
    AND MONTH(DataAgendada) = 7
    AND YEAR(DataAgendada)  = 2018
    AND FlagTarifado        = 1

SELECT  COUNT(*) AS QtdEnviados
FROM    tblLoteTESTE
WHERE   CodCampanha         = 1
    AND MONTH(DataAgendada) = 7
    AND YEAR(DataAgendada)  = 2018
    AND FlagEnviado         = 1

SELECT  COUNT(*) AS QtdRespondidos
FROM    tblLoteTESTE
WHERE   CodCampanha         = 1
    AND MONTH(DataAgendada) = 7
    AND YEAR(DataAgendada)  = 2018
    AND FlagRespondido      = 1

Could you help me?

sql sql-server
  • 1

    Have you tried UNION? Knows how to?

  • You can use all your selects as subselects of a main.

3 answers

3


Remove the filter for each flag and add conditioning, using the case when, you can put more clauses.

The idea is:

select sum(case when [condição específica que conta no campo_1]
                then 1 
                else 0
           end) campo_1,
       sum(case when [condição específica que conta no campo_2]
                then 1 
                else 0
           end) campo_2
  from tabela
 where [condições gerais para todos os campos totalizadores]

In your case it would look like this:

SELECT sum(case when FlagTarifado = 1 then 1 else 0 end) QtdTarifados,
       sum(case when FlagEnviado = 1 then 1 else 0 end) QtdEnviados,
       sum(case when FlagRespondido = 1 then 1 else 0 end) QtdRespondidos
  FROM tblLoteTESTE
 WHERE CodCampanha = 1 AND MONTH(DataAgendada) = 7 AND YEAR(DataAgendada) = 2018

  • Thank you very much Melissa ^^

1

Use Union all to join all your selects into one

SELECT COUNT(*) AS QtdTarifados  ,0  AS QtdEnviados   ,0  AS QtdRespondidos FROM tblLoteTESTE
WHERE CodCampanha = 1 AND MONTH(DataAgendada) = 7 AND YEAR(DataAgendada) = 2018  AND FlagTarifado = 1

UNION ALL

 SELECT 0 AS QtdTarifados  , COUNT(*)   AS QtdEnviados   ,0  AS QtdRespondidos FROM tblLoteTESTE                                    
WHERE CodCampanha = 1 AND MONTH(DataAgendada) = 7 AND YEAR(DataAgendada) = 2018  AND FlagEnviado = 1

UNION ALL


 SELECT 0 AS QtdTarifados  , 0  AS QtdEnviados  ,COUNT(*)   AS QtdRespondidos FROM tblLoteTESTE                                                    
WHERE CodCampanha = 1 AND MONTH(DataAgendada) = 7 AND YEAR(DataAgendada) = 2018  AND FlagRespondido = 1

0

There are several ways to achieve the desired result, one can be more costly at the processing level, so it is worth taking a look at it. For example, you can use as subquery within a main select:

SELECT 
(SELECT COUNT(*) FROM tblLoteTESTE
 WHERE CodCampanha = 1 AND MONTH(DataAgendada) = 7 AND YEAR(DataAgendada) = 2018  AND FlagTarifado = 1) AS QtdTarifados,

(SELECT COUNT(*) AS QtdEnviados  FROM tblLoteTESTE
 WHERE CodCampanha = 1 AND MONTH(DataAgendada) = 7 AND YEAR(DataAgendada) = 2018  AND FlagEnviado = 1) AS QtdEnviados,

(SELECT COUNT(*) AS QtdRespondidos  FROM tblLoteTESTE
 WHERE CodCampanha = 1 AND MONTH(DataAgendada) = 7 AND YEAR(DataAgendada) = 2018  AND FlagRespondido = 1) AS QtdRespondidos

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.