I'm doing a stored procedure that returns some data to me from some files that are in some tables, I want to count how many different file types there are but it gives me an erroneous amount I was suggested to use group by but my query is not there
The code I am using is the following:
USE [SEAA]
GO
/****** Object: StoredProcedure [dbo].[SelectArchivosXReferencia] Script Date: 07/16/2018 16:14:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SelectArchivosXReferencia] -- [SelectArchivosXReferencia] 'infi17-00008'
@referencia as varchar(50)
as
begin
DECLARE @CONTADOR AS INT
SET @CONTADOR=(SELECT DISTINCT COUNT(OPCT_TIPODOCTO.OPCT_TIPODOCTO_DESCRIPCION) as CONTADOR FROM OPCT_TIPODOCTO
INNER JOIN UTL_DOCUMENTOS ON OPCT_TIPODOCTO_CLAVE =UTL_DOCUMENTOS_TIPO
where (UTL_DOCUMENTOS.UTL_DOCUMENTOS_IDFORANEO = @referencia )group by OPCT_TIPODOCTO.OPCT_TIPODOCTO_DESCRIPCION,OPCT_TIPODOCTO.OPCT_TIPODOCTO_CLAVE,UTL_DOCUMENTOS.UTL_DOCUMENTOS_TIPO)
SELECT UTL_DOCUMENTOS.UTL_DOCUMENTOS_NOMBRE AS NOMBRE,UTL_DOCUMENTOS_ID AS ID,
replace(replace(UTL_DOCUMENTOS.UTL_DOCUMENTOS_PATH,'104.16.118.221','4chan.org'),'104.16.118.222','4chan.org') AS PATH, CASE WHEN (UTL_DOCUMENTOS.UTL_DOCUMENTOS_MIMETYPE = 'application/pdf')
THEN 'pdf.png' WHEN (UTL_DOCUMENTOS.UTL_DOCUMENTOS_MIMETYPE = 'application/rtf')
THEN 'word.png' WHEN (UTL_DOCUMENTOS.UTL_DOCUMENTOS_MIMETYPE = 'application/msword')
THEN 'word.png' WHEN (UTL_DOCUMENTOS.UTL_DOCUMENTOS_MIMETYPE = 'text/xml')
THEN 'xml2.png' WHEN (UTL_DOCUMENTOS.UTL_DOCUMENTOS_MIMETYPE = 'application/excel')
THEN 'excel.png' WHEN (UTL_DOCUMENTOS.UTL_DOCUMENTOS_MIMETYPE = 'xlsx')
THEN 'excel.png' WHEN (UTL_DOCUMENTOS.UTL_DOCUMENTOS_MIMETYPE = 'image/jpg')
THEN 'img.png' WHEN (UTL_DOCUMENTOS.UTL_DOCUMENTOS_MIMETYPE = 'image/jpeg')
THEN 'img.png' WHEN (UTL_DOCUMENTOS.UTL_DOCUMENTOS_MIMETYPE = 'image/gif')
THEN 'img.png' WHEN (UTL_DOCUMENTOS.UTL_DOCUMENTOS_MIMETYPE = 'image/png')
THEN 'img.png' WHEN (UTL_DOCUMENTOS.UTL_DOCUMENTOS_MIMETYPE = 'image/tif')
THEN 'img.png' WHEN (UTL_DOCUMENTOS.UTL_DOCUMENTOS_MIMETYPE = 'image/gif')
THEN 'img.png' WHEN (UTL_DOCUMENTOS.UTL_DOCUMENTOS_MIMETYPE = 'image/tiff')
THEN 'img.png' ELSE 'generic.png' END AS MIME, OPCT_TIPODOCTO.OPCT_TIPODOCTO_DESCRIPCION AS TIPODESCRIP,
@CONTADOR AS CONTADOR
FROM UTL_DOCUMENTOS INNER JOIN OPCT_TIPODOCTO ON OPCT_TIPODOCTO.OPCT_TIPODOCTO_CLAVE = UTL_DOCUMENTOS.UTL_DOCUMENTOS_TIPO
WHERE (UTL_DOCUMENTOS.UTL_DOCUMENTOS_IDFORANEO = @referencia)
--emanuel
and [UTL_DOCUMENTOS_TIPO] <>4
--edn emanuel
end