How to arm query with select distinguc count and group by in sql

0

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
    
asked by emanuelle 17.07.2018 в 17:32
source

0 answers