Can not perform an aggregate function on an expression containing an aggregate or a subquery [duplicate]

Question

Can not perform an aggregate function on an expression containing an aggregate or a subquery [duplicate]

Navigation

#1 by (0 votes)

-2

I have this query and it does not allow me to use an aggregation function within another aggregation function and the fact is that I want the fields not to be repeated and I have already tried with distinct and nothing.

this is the error:

Can not perform an aggregate function on an expression containing an aggregate or a subquery.

select  distinct CodigoDeAlquiler, 
        case datepart(weekday,FechaDelAlquiler)
        when 1 then 'Domingo' when 2 then 'Lunes'
        when 3 then 'Martes' when 4 then 'Miercoles'
        when 5 then 'Jueves' when 6 then 'Viernes'
        when 7 then 'Sabado' end  as Dia, 
        FechaRealDevolucion, 
        l.Titulo,
        cl.NumeroDeCopia, 
        c.NombresDelCliente + ' ' + c.ApellidosDelCliente as Cliente,
        sum(count(alq.CodigoDeAlquiler)) as Total
from AlquileresDelLibro alq
inner join Clientes c
on alq.ClienteId = c.Id
inner join CopiasDelLibro cl
on alq.CopiaDelLibroId = cl.Id
inner join Libros l
on cl.LibroId = l.Id
where datepart(week,FechaDelAlquiler) = datepart(week,getdate())
group by CodigoDeAlquiler, datepart(weekday,FechaDelAlquiler), FechaRealDevolucion, l.Titulo, cl.NumeroDeCopia, c.NombresDelCliente, c.ApellidosDelCliente
having count(*) > 0
order by Dia asc

sql c# sql-server

asked by Freison Castro 21.08.2018 в 20:11

source

1 answer

ajax + rails send ajax data to controller How to call the id of my SQL database and show it in a query with PHP?

score 0 · Answer 1

Use sub-query

select  CodigoDeAlquiler,
        Dia,
        FechaRealDevolucion,
        Titulo,
        NumeroDeCopia,
        Cliente,
        sum(Total) as Total
from (
            select     distinct 
                    CodigoDeAlquiler, 
                    case datepart(weekday,FechaDelAlquiler)
                    when 1 then 'Domingo' when 2 then 'Lunes'
                    when 3 then 'Martes' when 4 then 'Miercoles'
                    when 5 then 'Jueves' when 6 then 'Viernes'
                    when 7 then 'Sabado' end  as Dia, 
                    FechaRealDevolucion, 
                    l.Titulo,
                    cl.NumeroDeCopia, 
                    c.NombresDelCliente + ' ' + c.ApellidosDelCliente as Cliente,
                    count(alq.CodigoDeAlquiler) as Total
            from AlquileresDelLibro alq
            inner join Clientes c
            on alq.ClienteId = c.Id
            inner join CopiasDelLibro cl
            on alq.CopiaDelLibroId = cl.Id
            inner join Libros l
            on cl.LibroId = l.Id
            where datepart(week,FechaDelAlquiler) = datepart(week,getdate())
            group by CodigoDeAlquiler, datepart(weekday,FechaDelAlquiler), FechaRealDevolucion, l.Titulo, cl.NumeroDeCopia, c.NombresDelCliente, c.ApellidosDelCliente
            having count(*) > 0
            order by Dia asc    ) as x
group by CodigoDeAlquiler, Dia, FechaRealDevolucion, Titulo, NumeroDeCopia, Cliente