Error in SQL statement with Count Distinct

Question

Error in SQL statement with Count Distinct

Navigation

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

1

I'm trying to perform a SQL query to a database to get the number of pieces, for each position-phase and I used the following query:

SELECT 
    maquina AS PUESTO,
    fase AS FASE,
    COUNT(DISTINCT pieza) AS PIEZAS
FROM tabla1
LEFT JOIN tabla2 on tabla1.pieza=tabla2.pieza
LEFT JOIN table3 on tabla2.bol=tabla3.bol
WHERE bol ='bol1'
AND convert(varchar, CONVERT(date, fecha, 106),103) >= CAST('30/11/2018' AS DATE) 
AND convert(varchar, CONVERT(date, fecha, 106),103) <= CAST('30/11/2018' AS DATE)
AND tabla1.pieza NOT LIKE ''
GROUP BY PUESTO, FASE
ORDER BY PUESTO

and return the following:

I repeat the piece count for each position and phase, when what I want is to see only the account of each position-phase

sql sql-server

asked by marcss 30.11.2018 в 11:36

source

2 answers

WebRequest SOAP with WSS-PasswordType PasswordText send array from ajax to php

score 0 · Answer 1

To get the rows in common, make an inner join instead of a left join. That is:

SELECT 
    maquina AS PUESTO,
    fase AS FASE,
    COUNT(DISTINCT pieza) AS PIEZAS
FROM tabla1
INNER JOIN tabla2 on tabla1.pieza=tabla2.pieza
INNER JOIN table3 on tabla2.bol=tabla3.bol
WHERE bol ='bol1'
AND convert(varchar, CONVERT(date, fecha, 106),103) >= CAST('30/11/2018' AS DATE) 
AND convert(varchar, CONVERT(date, fecha, 106),103) <= CAST('30/11/2018' AS DATE)
AND tabla1.pieza NOT LIKE ''
GROUP BY PUESTO, FASE
ORDER BY PUESTO

score 0 · Answer 2

To get the rows in common, to a natural inner join instead of left join. That is to say:

SELECT 
    maquina AS PUESTO,
    fase AS FASE,
    COUNT(distinct pieza) AS PIEZAS
FROM tabla1
NATURAL INNER JOIN tabla2
NATURAL INNER JOIN table3 
WHERE bol ='bol1'
AND convert(varchar, CONVERT(date, fecha, 106),103) >= CAST('30/11/2018' AS DATE) 
AND convert(varchar, CONVERT(date, fecha, 106),103) <= CAST('30/11/2018' AS DATE)
AND (tabla1.pieza) NOT LIKE ''
GROUP BY PUESTO, FASE
ORDER BY PUESTO;