SELECT di una colonna e della somma di un campo

mercoledì 16 dicembre 2009 - 13.27

paquito_ita Profilo | Senior Member

Ciao,

devo eseguire una semplice query in cui seleziono dei campi e un la somma di un' altra colonna:
SELECT Name, SUM(SessionDUration) AS TOTAL FROM ConferenceLog WHERE ConferenceStarted >= (GETDATE()-90) GROUP BY AppInstanceID

E questa query funziona bene. Tuttavia devo anche selezionare, nella stessa query, un ulteriore campo nella select:
SELECT ID, Name, SUM(SessionDUration) AS TOTAL FROM ConferenceLog WHERE ConferenceStarted >= (GETDATE()-90) GROUP BY AppInstanceID

Al che viene generata l'eccezione: "Column 'ConferenceLog.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Come potrei modificare la query per ottenere come risultato: ID, Name, Total ?

Grazie

lbenaglia Profilo | Guru

>devo eseguire una semplice query in cui seleziono dei campi e
>un la somma di un' altra colonna:
>
>SELECT Name, SUM(SessionDUration) AS TOTAL
>FROM ConferenceLog
>WHERE ConferenceStarted >= (GETDATE()-90) GROUP BY AppInstanceID
>
>
>E questa query funziona bene.
Se non raggruppi anche per Name dubito che la query funzioni bene

>SELECT ID, Name, SUM(SessionDUration) AS TOTAL
>FROM ConferenceLog
>WHERE ConferenceStarted >= (GETDATE()-90) GROUP BY AppInstanceID
Qui devi raggruppare ALMENO per ID e Name.
In generale devi specificare nella clausola GROUP BY tutte le colonne della SELECT list alle quali non hai applicato una funzione di aggregazione.

>Al che viene generata l'eccezione: "Column 'ConferenceLog.ID'
>is invalid in the select list because it is not contained in
>either an aggregate function or the GROUP BY clause."
Appunto

>Come potrei modificare la query per ottenere come risultato:
>ID, Name, Total ?
Specifica ID, Name nella clausola GROUP BY.

>Grazie
Prego.

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

paquito_ita Profilo | Senior Member

>Se non raggruppi anche per Name dubito che la query funzioni
>bene
Ho rinominato nel forum AppInstanceID in Name e mi son dimenticato di rinominare anche nel Group BY

>In generale devi specificare nella clausola GROUP BY tutte le colonne della SELECT list alle quali non hai applicato una funzione
>di aggregazione.
Ho già provato in questo modo
select ID, Name, SUM(SessionDuration) AS TOTAL from ConferenceLog where ConferenceStarted >= (GETDATE()-90) AND MediaType = 1 group by Name, ID
ma il risultato non è corretto, in quanto invece che avere una riga "unica" per ogni NAME con la relativa somma, ho tante righe quante sarebbero rotornate senza usare SUM e group by, ovviamente in quanto l'ID in questo caso è diverso.
Tuttavia dato che l'ID in questo caso mi serve solo per identificare la riga risultante e non quella effettiva, magari potrei creare un campo ad hoc che, per ogni riga, si incrementa di uno.
Sapresti suggerirmi come ottenere una cosa del genere? In tal modo "newID" sarebbe presente solo una volta per riga.
select INC() as newID, Name, SUM(SessionDuration) AS TOTAL from ConferenceLog where ConferenceStarted >= (GETDATE()-90) AND MediaType = 1 group by Name, newID

Grazie



Grazie

lbenaglia Profilo | Guru

>Ho già provato in questo modo
>
>select ID, Name, SUM(SessionDuration) AS TOTAL
>from ConferenceLog
>where ConferenceStarted >= (GETDATE()-90) AND MediaType = 1
>group by Name, ID
>
>ma il risultato non è corretto, in quanto invece che avere una
>riga "unica" per ogni NAME con la relativa somma, ho tante righe
>quante sarebbero rotornate senza usare SUM e group by, ovviamente
>in quanto l'ID in questo caso è diverso.
Eh certo, hai chiesto al DBMS di eseguire esattamente quel raggruppamento ed il risultato è CORRETTO
Ora, se posti un esempio completo con la struttura della tabella (CREATE TABLE), alcune righe di prova (INSERT INTO) ed il result set che vorresti ottenere con quei dati ti mostrerò come risolvere il problema scrivendo una query basata o su una CTE oppure su una tabella derivata.

>Grazie
Prego.

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

paquito_ita Profilo | Senior Member

Ecco il codice:
CREATE TABLE [ConferenceLog]( [ID] [int] IDENTITY(1,1) NOT NULL, [AppInstanceID] [uniqueidentifier] NULL, [SessionDuration] [int] NULL) INSERT INTO [ConferenceLog] ([AppInstanceID], [SessionDuration]) VALUES ('4B0EA86B-81E0-4BDE-A4A4-D4203B3317A0',150), ('4B0EA86B-81E0-4BDE-A4A4-D4203B3317A0',30), ('F9F44FD9-491D-4903-BF79-524444353329',10)

L'output desiderato sarebbe:
ID AppInstanceID Usage --- ----------------------------------------------- --------- 1 F9F44FD9-491D-4903-BF79-524444353329 10 2 4B0EA86B-81E0-4BDE-A4A4-D4203B3317A0 180 ....

Grazie

lbenaglia Profilo | Guru

>Ecco il codice:
>
>CREATE TABLE [ConferenceLog](
> [ID] [int] IDENTITY(1,1) NOT NULL,
> [AppInstanceID] [uniqueidentifier] NULL,
> [SessionDuration] [int] NULL)
>
>INSERT INTO [ConferenceLog]
> ([AppInstanceID],
> [SessionDuration])
> VALUES
> ('4B0EA86B-81E0-4BDE-A4A4-D4203B3317A0',150),
> ('4B0EA86B-81E0-4BDE-A4A4-D4203B3317A0',30),
> ('F9F44FD9-491D-4903-BF79-524444353329',10)
>
>
>L'output desiderato sarebbe:
>
>ID AppInstanceID
>Usage
>--- -----------------------------------------------
>---------
>1 F9F44FD9-491D-4903-BF79-524444353329
>10
>2 4B0EA86B-81E0-4BDE-A4A4-D4203B3317A0
>180
>
>....


Come fa la riga F9F44FD9-491D-4903-BF79-524444353329 avere ID = 1 se in fase di INSERT il valore generato è 3?

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

paquito_ita Profilo | Senior Member

>Come fa la riga F9F44FD9-491D-4903-BF79-524444353329 avere ID= 1 se in fase di INSERT il valore generato è 3?

Devo usare tale query in un programma dove obbligatoriamente devo specificare una colonna di nome ID come primary key.
Dato che, appunto per la struttura della query, non posso usare il vero ID (dato che nel caso di SUM() dovrei poi raggrupparlocon GROUP BY), ne creerei uno fittizio che viene automaticamente incrementato.

Quello che a me serve sono solo le colonne AppInstanceID e SUM(...). Tuttavia per via dell'uso della primary key, devo anche specificare un ID (da qui il valore "1" fittizio...)

lbenaglia Profilo | Guru

>Devo usare tale query in un programma dove obbligatoriamente
>devo specificare una colonna di nome ID come primary key.
>Dato che, appunto per la struttura della query, non posso usare
>il vero ID (dato che nel caso di SUM() dovrei poi raggrupparlocon
>GROUP BY), ne creerei uno fittizio che viene automaticamente
>incrementato.

Se ti serve semplicemente l'univocità allora perché non usare quello che hai già?

USE tempdb; CREATE TABLE dbo.ConferenceLog( ID int IDENTITY(1,1) NOT NULL, AppInstanceID uniqueidentifier NULL, SessionDuration int NULL ) INSERT dbo.ConferenceLog VALUES ('4B0EA86B-81E0-4BDE-A4A4-D4203B3317A0', 150) , ('4B0EA86B-81E0-4BDE-A4A4-D4203B3317A0', 30) , ('F9F44FD9-491D-4903-BF79-524444353329', 10); WITH CTE_GetKey(ID, AppInstanceID, Usage) AS ( SELECT MIN(ID), AppInstanceID, SUM(SessionDuration) FROM dbo.ConferenceLog GROUP BY AppInstanceID ) SELECT CTE.* FROM dbo.ConferenceLog AS CL JOIN CTE_GetKey AS CTE ON CL.ID = CTE.ID AND CL.AppInstanceID = CTE.AppInstanceID; /* Output: ID AppInstanceID Usage ----------- ------------------------------------ ----------- 1 4B0EA86B-81E0-4BDE-A4A4-D4203B3317A0 180 3 F9F44FD9-491D-4903-BF79-524444353329 10 (2 row(s) affected) */ DROP TABLE dbo.ConferenceLog;

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org

paquito_ita Profilo | Senior Member

>Se ti serve semplicemente l'univocità allora perché non usare quello che hai già?
Eh eh, perchè non avevo pensato di prendere solo un ID (con min(ID))

Grazie mille per il consiglio!!
Solo un'ultima domanda. Perchè fare il JOIN e usare WITH CTE invece che semplicemente:
SELECT MIN(ID), AppInstanceID, SUM(SessionDuration) FROM dbo.ConferenceLog where MediaType = 1 AND ConferenceStarted >= (GETDATE() -90) GROUP BY AppInstanceID

lbenaglia Profilo | Guru

>Solo un'ultima domanda. Perchè fare il JOIN e usare WITH CTE
>invece che semplicemente:
>
> SELECT MIN(ID), AppInstanceID, SUM(SessionDuration)
> FROM dbo.ConferenceLog
>where MediaType = 1 AND ConferenceStarted >= (GETDATE() -90)
> GROUP BY AppInstanceID

Perché sto facendo 49564356743659438 cose insieme e mi sono "sconfonduto"
Ovviamente la tua query è quella più efficiente

Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org
Partecipa anche tu! Registrati!
Hai bisogno di aiuto ?
Perchè non ti registri subito?

Dopo esserti registrato potrai chiedere
aiuto sul nostro Forum oppure aiutare gli altri

Consulta le Stanze disponibili.

Registrati ora !
Copyright © dotNetHell.it 2002-2023
Running on Windows Server 2008 R2 Standard, SQL Server 2012 & ASP.NET 3.5