Query che restituisca colonne di valori e come ultima riga la somma de...

giovedì 02 aprile 2009 - 13.02

Elenco Threads

Stanze Forum

Aggiungi ai Preferiti

Cerca nel forum

gonzalezh Profilo | Junior Member

71 messaggi | Data Invio: gio 2 apr 2009 - 13:02

Buongiorno,

Io ho una query che mi restituisce dei tempi in minuti, per fare questo fa dei cast su delle date. Ora vorrei come ultima riga il totale dei minuti ma non riesco ad impostare correttamente la query.
Ho visto questo post (http://www.dotnethell.it/forum/messages.aspx?ThreadID=25559) ma impostando similarmente la mia query ottengo questo errore:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

La query è questa:

DECLARE @UidSess Int
SET @UidSess=341

SELECT ELB_TYPE AS Fase,ROUND(CAST(SUM(CAST ((END_TIME-STR_TIME) AS Float)) AS Float)*24*60, 2) AS MinutiTotali,
ROUND(CAST(CAST(MAX(END_TIME)-MIN(STR_TIME)AS Float)AS Float)*24*60, 2) AS MinutiElapsed,
ROUND(CAST(SUM(CAST((END_TIME-STR_TIME) AS Float)) AS Float)*24, 2) AS OreTotali,
ROUND(CAST(CAST(MAX(END_TIME)-MIN(STR_TIME)AS Float)AS Float)*24, 2) AS OreElapsed
FROM T_SYS_ELB_ELB_ANAG
WHERE uid_ses=@UidSess and UID_ELB>=(select MAX(UID_ELB) from T_SYS_ELB_ELB_ANAG
WHERE ELB_TYPE=0 and uid_ses=@UidSess)
GROUP BY ELB_TYPE

io ho aggiunto alla fine della query questo:
UNION ALL
SELECT 'TOTALE',sum(ROUND(CAST(SUM(CAST ((END_TIME-STR_TIME) AS Float)) AS Float)*24*60, 2)),
sum(ROUND(CAST(CAST(MAX(END_TIME)-MIN(STR_TIME)AS Float)AS Float)*24*60, 2)),
sum(ROUND(CAST(SUM(CAST((END_TIME-STR_TIME) AS Float)) AS Float)*24, 2)) ,
sum(ROUND(CAST(CAST(MAX(END_TIME)-MIN(STR_TIME)AS Float)AS Float)*24, 2))
FROM T_SYS_ELB_ELB_ANAG
WHERE uid_ses=@UidSess and UID_ELB>=(select MAX(UID_ELB) from T_SYS_ELB_ELB_ANAG
WHERE ELB_TYPE=0 and uid_ses=@UidSess)
ma mi viene fuori l'errore sopra riportato.

Mi sapete aiutare??

Grazie,

Helen.

gonzalezh Profilo | Junior Member

71 messaggi | Data Invio: ven 3 apr 2009 - 12:33

Ho risolto così:

DECLARE @UidSess Int
SET @UidSess=341

SELECT Cast(ELB_TYPE as varchar) AS Fase,
ROUND(CAST(SUM(CAST ((END_TIME-STR_TIME) AS Float)) AS Float)*24*60, 2) AS MinutiTotali,
ROUND(CAST(CAST(MAX(END_TIME)-MIN(STR_TIME)AS Float)AS Float)*24*60, 2) AS MinutiElapsed,
ROUND(CAST(SUM(CAST((END_TIME-STR_TIME) AS Float)) AS Float)*24, 2) AS OreTotali,
ROUND(CAST(CAST(MAX(END_TIME)-MIN(STR_TIME)AS Float)AS Float)*24, 2) AS OreElapsed
FROM T_SYS_ELB_ELB_ANAG
WHERE uid_ses=@UidSess and UID_ELB>=(select MAX(UID_ELB) from T_SYS_ELB_ELB_ANAG
WHERE ELB_TYPE=0 and uid_ses=@UidSess)
GROUP BY ELB_TYPE
UNION ALL
SELECT 'TOTALE',ROUND(CAST(SUM(CAST ((END_TIME-STR_TIME) AS Float)) AS Float)*24*60, 2),
ROUND(CAST(CAST(MAX(END_TIME)-MIN(STR_TIME)AS Float)AS Float)*24*60, 2),
ROUND(CAST(SUM(CAST((END_TIME-STR_TIME) AS Float)) AS Float)*24, 2),
ROUND(CAST(CAST(MAX(END_TIME)-MIN(STR_TIME)AS Float)AS Float)*24, 2)
FROM T_SYS_ELB_ELB_ANAG
WHERE uid_ses=@UidSess and UID_ELB>=(select MAX(UID_ELB) from T_SYS_ELB_ELB_ANAG
WHERE ELB_TYPE=0 and uid_ses=@UidSess)

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 !