How to join 3 Select in one

Question

How to join 3 Select in one

Navigation

#1 by (3 votes)
#2 by (1 votes)
#3 by (0 votes)

0

I would like to know how I could make a SELECT where I can bring the same but result in a single SELECT .

Select example I need to be unique:

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

asked by anonymous 12.07.2018 / 15:03

3 answers

1

Use Union all to merge 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

12.07.2018 / 15:15

0

There are several ways to get the desired result, some can be more costly at the processing level, so it's worth paying attention to it. For example, you can use as subquery inside 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

12.07.2018 / 15:20

How to access a jquery without id? Php Numerical format of 000 000 000 0 [duplicate]

score 3 · Accepted Answer

Remove the filter for each flag and add conditioning, using 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