Count rows in with a SQL Server subquery

Question

Count rows in with a SQL Server subquery

Navigation

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

3

I have the following query :

SELECT DISTINCT 
     NoEconomico
    ,count(NoEconomico) AS Totales
FROM prm_FEMSA_IndicadoresTelemetria
WHERE RPMEventos <> 0
    AND AccountId = 9159
    AND FechaInicio BETWEEN '2016-11-27'
        AND '2016-12-31'
    AND CeEmplazamiento LIKE 'Celaya'
GROUP BY NoEconomico

That throws me the following data:

**NoEconomico              Totales**
CL0201/CEL10017          2
RCL018/CEL17698          5
RCL023/CEL10940          3
RCL026/CEL10931          2
RCL031/CEL10934          2
RCL033/CEL8243           4
RCL036/CEL17705          5
RCL042/CEL10945          3
RCL047-RCL048/CEL6957    5
RCL049-RCL050/CEL11449   5
RCL056/CEL8139           2
RCL102/CEL17695          1
RCL103/CEL10941          5
RCL105/CEL10939          1
RCL109/CEL10946          3
RCL113/CEL17703          2
RCL115/CEL17701          5
RCL122/CEL9986           1
RCL123/CEL6955           2
RCL135/CEL10932          3
RCL140/CEL17704          2
RCL141/CEL10938          2
RCL144/CEL17699          5
RCL145/CEL17700          5
RCL146/CEL11462          5
RCL154/CEL10935          3
RCL155/CEL17696          4
RCLD01/CEL10949          1
RCLTC2/CEL17702          5
RESERVA/CEL16138         2
RESERVA/CEL16177         1
RESERVA/CEL9897          3
RESERVA/CEL9927          2
RTL264/CEL18253          1

My question is, how can I do it to tell me the total of the rows (records), but without adding the totals, that is, the NoEconomico . In total I would be counting 34 records, I hope I explained.

My desired result would be like this:

CeEmplazamiento   Total
Celaya              34

sql sql-server

asked by Ric_hc 22.03.2017 в 19:32

source

3 answers

1

If you only want to count the rows you can use a COUNT:

SELECT t.CeEmplazamiento , COUNT(1) FROM (select distinct CeEmplazamiento ,NoEconomico,
        count(NoEconomico) as Totales
        from  prm_FEMSA_IndicadoresTelemetria  
        where RPMEventos <> 0 and AccountId = 9159  and FechaInicio between '2016-11-27' and '2016-12-31' and CeEmplazamiento like 'Celaya'
        group by CeEmplazamiento ,NoEconomico) t
        GROUP BY t.CeEmplazamiento

answered by 22.03.2017 в 19:36

1

Simply doing the COUNT of NoEconomico

SELECT
    CeEmplazamiento
    ,COUNT(DISTINCT NoEconomico) AS Total
FROM prm_FEMSA_IndicadoresTelemetria
WHERE RPMEventos <> 0
    AND AccountId = 9159
    AND FechaInicio BETWEEN '2016-11-27'
    AND '2016-12-31'
    AND CeEmplazamiento LIKE 'Celaya'
GROUP BY CeEmplazamiento

answered by 22.03.2017 в 19:36

How can I hide a query string from the url? Ajax query "undefined"

score 0 · Accepted Answer

I do not fully understand why you need to return CeEmplazamiento in the result, since it is part of one of the filters. Unless the filter can be adjusted so that several values for CeEmplazamiento are possible.

In any case, this you would do with a GROUP BY in a subquery within another GROUP BY :

SELECT CeEmplazamiento, COUNT(*)
  FROM (SELECT CeEmplazamiento, NoEconomico
          FROM prm_FEMSA_IndicadoresTelemetria
         WHERE RPMEventos <> 0
           AND AccountId = 9159
           AND FechaInicio BETWEEN '2016-11-27' AND '2016-12-31'
           AND CeEmplazamiento LIKE 'Celaya'
         GROUP BY CeEmplazamiento, NoEconomico) t
 GROUP BY CeEmplazamiento

A better option that occurs to me is to use a COUNT(DISTINCT ..) . This simplifies the query considerably:

SELECT CeEmplazamiento, COUNT(DISTINCT NoEconomico)
  FROM prm_FEMSA_IndicadoresTelemetria
 WHERE RPMEventos <> 0
   AND AccountId = 9159
   AND FechaInicio BETWEEN '2016-11-27' AND '2016-12-31'
   AND CeEmplazamiento LIKE 'Celaya'
 GROUP BY CeEmplazamiento