Grupo SQL por referencia externa después de 2 uniones internas

Grupo SQL por referencia externa después de 2 uniones internas

Mi consulta sql no funciona según lo previsto, en su forma actual arrojará resultados, pero algunos de ellos en la segunda columna están duplicados, o en otras palabras, algunos nombres de computadora seguidos de la letra de la unidad se muestra dos veces.

La consulta es esta:

(select Distinct 'ServerDrive' as storageType, cast(assetname as 
varchar)+ '\ ' +cast(caption as varchar) as storagePath,((rqDiskdrivesHist. 
[size]-rqDiskdrivesHist.[freespace])/1024/1024) as usedMB,(rqDiskdrivesHist. 
[size]/1024/1024) as sizeMB from lansweeperdb.dbo.tblAssets 
inner join rqDiskdrivesHist
on lansweeperdb.dbo.tblAssets.AssetID=rqDiskdrivesHist.AssetID 
where drivetype=3
and lansweeperdb.dbo.tblAssets.AssetName in
(select distinct entitypath from rqcentities
inner join rqcustomer
on rqcentities.customerID=rqcustomer.customerID 
where customername like '%ak 26%' and entityType='computer'
and not (
(AssetName like 'WFP3675' and caption like 'D:')
or
(AssetName like 'WFP3675' and caption like 'E:')
or
(AssetName like 'WFP3675' and caption like 'F:')
or
(AssetName like '%00500%')
)  
)) 

Si intento agruparme al final. con:

GROUP BY cast(assetname as varchar)+'\'+cast(caption as varchar)

Obtendré el mensaje "Cada expresión GROUP BY debe contener al menos una columna que no sea una referencia externa. " error.

Editar: Estas son 2 filas de los resultados que están duplicados.

ingrese la descripción de la imagen aquí

Mostrar la mejor respuesta

El espacio después de la barra inclinada se agregó solo para colorear el código. cast(assetname as varchar)+ '\ ' +cast(caption as varchar) debe tratarse como cast(assetname as varchar)+ '\' +cast(caption as varchar).

Los datos de muestra se sirven mejor como DDL + DML. edite su pregunta para incluirla y los resultados deseados.

Debe agrupar en el alias en su lugar, ya que no expuso esos campos en la consulta interna.

SELECT
    storageType,
    storagePath,
    MaxMB = MAX(usedMB),
    MinMB = MIN(usedMB),
    AvgMB = AVG(usedMB)
FROM
(
    select 'ServerDrive' as storageType, 
    cast(assetname as varchar)+ '\ ' +cast(caption as varchar) as storagePath,
    ((rqDiskdrivesHist.[size]-rqDiskdrivesHist.[freespace])/1024/1024) as usedMB,
    (rqDiskdrivesHist.[size]/1024/1024) as sizeMB 
    from 
        lansweeperdb.dbo.tblAssets 
        inner join rqDiskdrivesHist on lansweeperdb.dbo.tblAssets.AssetID=rqDiskdrivesHist.AssetID 
    where 
        drivetype=3
        and lansweeperdb.dbo.tblAssets.AssetName in
        (
            select distinct entitypath 
            from rqcentities
            inner join rqcustomer on rqcentities.customerID=rqcustomer.customerID 
            where customername like '%ak 26%' and entityType='computer'
            and not (
                        (AssetName like 'WFP3675' and caption like 'D:')
                        or
                        (AssetName like 'WFP3675' and caption like 'E:')
                        or
                        (AssetName like 'WFP3675' and caption like 'F:')
                        or
                        (AssetName like '%00500%')
                    )  
        )
) AS X
GROUP BY
    storagePath,
    storageType

¿Podría mostrar cómo se vería toda la consulta?

@lason: se agregó una forma de devolver un registro para cada ruta y tipo.

@lason: tenga en cuenta que eliminé el distintivo ya que es un campo de grupo. Además, realmente no necesita el tipo de almacenamiento en la unión interna, ya que lo limita a un valor codificado.