How can I do Two differentes sum in the same query

[hortensiam]

hi!,
I need two types of sums in the same query. One type of sum correspond to the -group by- selection and the other type of sum correspond to a general total- .
I think something like that:

SELECT especiali,partido,descrip,
sum( case when esp_dedi='E' then 1 else 0 end) as count_E,
sum( case when esp_dedi='D' then 1 else 0 end) as count_D
FROM MED_ESP INNER JOIN
ESPECIAL ON MED_ESP.ESPECIALI = ESPECIAL.CODIGO INNER JOIN PARTIDO ON MED_ESP.PARTIDO = PARTIdO.NROPARTIDO
group by
MED_ESP.especiali,
ESPECIAL.descripcio,
MED_ESP.partido

but i need the totals group by only for mes_esp.partido in the same query!!!!

 

[vbkris]

not in the same query, however u could try this:

declare @total_var int --change as per need
select @total_var int = sum(column) from MED_ESP.partido

SELECT especiali,partido,descrip,
sum( case when esp_dedi='E' then 1 else 0 end) as count_E,
sum( case when esp_dedi='D' then 1 else 0 end) as count_D,@total_var as TotalSum
FROM MED_ESP INNER JOIN
ESPECIAL ON MED_ESP.ESPECIALI = ESPECIAL.CODIGO INNER JOIN PARTIDO ON MED_ESP.PARTIDO = PARTIdO.NROPARTIDO
group by
MED_ESP.especiali,
ESPECIAL.descripcio,
MED_ESP.partido

Known is handfull, Unknown is worldfull

 

[hortensiam]

Thanks for your attention!,
But i need all in only one recordset, to send the recordset to Crystal Report.

 

[vbkris]

that will give only one recordset. the first select query will not return any data.

Known is handfull, Unknown is worldfull

 

[SQLDenis]

the first 'recordset' is stored in a variable named @total_var
if you look down in the query you will see this line

sum( case when esp_dedi='D' then 1 else 0 end) as count_D,@total_var as TotalSum


notice the '@total_var as TotalSum' part?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[hortensiam]

The query is clear. But I've differents values of med_esp-partido!!

 

[vbkris]

could u be a bit more clear?

Known is handfull, Unknown is worldfull

 

[hortensiam]

Thank vbkris & SQLDenis,
I resolved my problem because of your help!

 

[vbkris]

sure...

Known is handfull, Unknown is worldfull