SELECT di una colonna e della somma di un campo

mercoledì 16 dicembre 2009 - 13.27

Elenco Threads

Stanze Forum

Aggiungi ai Preferiti

Cerca nel forum

paquito_ita Profilo | Senior Member

224 messaggi | Data Invio: mer 16 dic 2009 - 13:27

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

5.625 messaggi | Data Invio: mer 16 dic 2009 - 13:47

>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

224 messaggi | Data Invio: mer 16 dic 2009 - 14:13

>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

5.625 messaggi | Data Invio: mer 16 dic 2009 - 21:03

>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

224 messaggi | Data Invio: gio 17 dic 2009 - 09:37

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

5.625 messaggi | Data Invio: gio 17 dic 2009 - 10:28

>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

224 messaggi | Data Invio: gio 17 dic 2009 - 10:47

>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

5.625 messaggi | Data Invio: gio 17 dic 2009 - 10:57

>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

224 messaggi | Data Invio: gio 17 dic 2009 - 11:05

>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

5.625 messaggi | Data Invio: gio 17 dic 2009 - 11:07

>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

Torna su

Stanze Forum

Elenco Threads

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 !