QUERY WITH COUNT

I need to create a drill down report with counts at each level, I cant use matrix, i need to implement this using SQL query..The format looks like below

I need to get count of the field employee id for each region 1 through 8 and for each status value
*, 0, 1 ,2 ,3 ,4

STATUS

* 0 1 2 3 4

+region 1 count(id) count(id) count(id) count(id) count(id) cnt(id)

region 2 count(id) count(id) count(id) count(id) count(id) cnt(id)

-region 8

+school count(id) count(id) count(id) count(id) count(id) cnt(id)

The fields are in the same table

employee ID region status

A 1 1

B 1 0

C 2 3

Please help

THANKS

This should do, just expand out to 8 status:

create table matrixThang

(

employeeId char(1),

region int,

status int

)

insert into matrixThang

select 'A',1,1

union all

select 'B',1,0

union all

select 'C',2,3

go

select region, count(*) as ,

sum(case when status = 0 then 1 else 0 end) as [0],

sum(case when status = 1 then 1 else 0 end) as [1],

sum(case when status = 2 then 1 else 0 end) as [2],

sum(case when status = 3 then 1 else 0 end) as [3]

from matrixThang

group by region

Returns:

region * 0 1 2 3
-- -- -- -- -- --
1 2 1 1 0 0
2 1 0 0 0 1

|||

hi

Sorry , I am a little confused

does this give the count of employees for diffetent status?

I need to display count(employee id) for each region for each status

also i will need the count at each row level for each branch the employees attend

thanks

|||

Yes, the SUM is a trick to eliminate NULLs, as well as give you a lot of power over the types of aggregations you want. If you need to do something with duplicate values and count distincts like this, you can do something along these lines:

select region, count(*) as ,

count(distinct case when status = 0 then employeeId else NULL end) as [0],

count(distinct case when status = 1 then employeeId else NULL end) as [1],

count(distinct case when status = 2 then employeeId else NULL end) as [2],

count(distinct case when status = 3 then employeeId else NULL end) as [3]

from matrixThang

group by region

The NULL values will give you this warning message:

Warning: Null value is eliminated by an aggregate or other SET operation.

If you understand why you are getting this error message, you can use:

SET ANSI_WARNINGS OFF

to turn them off. Note that it obviscates problems, but the warning messages aren't a big deal either.

QUERY WITH COUNT

I need to create a drill down report with counts at each level, I cant use matrix, i need to implement this using SQL query..The format looks like below

I need to get count of the field employee id for each region 1 through 8 and for each status value
*, 0, 1 ,2 ,3 ,4

STATUS

* 0 1 2 3 4

+region 1 count(id) count(id) count(id) count(id) count(id) cnt(id)

region 2 count(id) count(id) count(id) count(id) count(id) cnt(id)

-region 8

+school count(id) count(id) count(id) count(id) count(id) cnt(id)

The fields are in the same table

employee ID region status

A 1 1

B 1 0

C 2 3

Please help

THANKS

This should do, just expand out to 8 status:

create table matrixThang

(

employeeId char(1),

region int,

status int

)

insert into matrixThang

select 'A',1,1

union all

select 'B',1,0

union all

select 'C',2,3

go

select region, count(*) as ,

sum(case when status = 0 then 1 else 0 end) as [0],

sum(case when status = 1 then 1 else 0 end) as [1],

sum(case when status = 2 then 1 else 0 end) as [2],

sum(case when status = 3 then 1 else 0 end) as [3]

from matrixThang

group by region

Returns:

region * 0 1 2 3
-- -- -- -- -- --
1 2 1 1 0 0
2 1 0 0 0 1

|||

hi

Sorry , I am a little confused

does this give the count of employees for diffetent status?

I need to display count(employee id) for each region for each status

also i will need the count at each row level for each branch the employees attend

thanks

|||

Yes, the SUM is a trick to eliminate NULLs, as well as give you a lot of power over the types of aggregations you want. If you need to do something with duplicate values and count distincts like this, you can do something along these lines:

select region, count(*) as ,

count(distinct case when status = 0 then employeeId else NULL end) as [0],

count(distinct case when status = 1 then employeeId else NULL end) as [1],

count(distinct case when status = 2 then employeeId else NULL end) as [2],

count(distinct case when status = 3 then employeeId else NULL end) as [3]

from matrixThang

group by region

The NULL values will give you this warning message:

Warning: Null value is eliminated by an aggregate or other SET operation.

If you understand why you are getting this error message, you can use:

SET ANSI_WARNINGS OFF

to turn them off. Note that it obviscates problems, but the warning messages aren't a big deal either.

Query top x with sum

This is a multi-part message in MIME format.
--=_NextPart_000_001E_01C57BFE.5877E810
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hello everyone,
I'm making a query that is supposed to sum the 3 best results.
The query is this:
SELECT top 3 classificacao_principal.id_equipa AS equipa, = SUM(classificacao_principal.posicao) AS soma_pontos
FROM classificacao_principal INNER JOIN atleta ON classificacao_principal.dorsal =3D = atleta.dorsal_atl
WHERE (atleta.sexo_atl =3D 'F' AND = classificacao_principal.id_equipa=3D'46')
GROUP BY classificacao_principal.id_equipa
ORDER BY soma_pontos ASC
This query resturns me the top 3 results, but the SUM is made with all = the results available. What i want is to sum only the 3 best results.
Any ideas?
--=_NextPart_000_001E_01C57BFE.5877E810
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hello everyone,

I'm making a query that is supposed to = sum the 3 best results.
The query is this:

SELECT top 3 = classificacao_principal.id_equipa AS equipa, SUM(classificacao_principal.posicao) AS soma_pontosFROM classificacao_principal
INNER JOIN atleta ON = classificacao_principal.dorsal =3D atleta.dorsal_atlWHERE (atleta.sexo_atl =3D 'F' AND classificacao_principal.id_equipa=3D'46')GROUP BY classificacao_principal.id_equipaORDER BY = soma_pontos ASC

This query resturns me the top 3 = results, but the SUM is made with all the results available. What i want is to sum = only the 3 best results.
Any ideas?
--=_NextPart_000_001E_01C57BFE.5877E810--Try,
select
id_equipa,
sum(posicao) as soma_pontos
from
(
SELECT top 3
classificacao_principal.id_equipa AS equipa,
classificacao_principal.posicao
FROM
classificacao_principal
INNER JOIN
atleta
ON classificacao_principal.dorsal = atleta.dorsal_atl
WHERE
atleta.sexo_atl = 'F'
AND classificacao_principal.id_equipa='46'
order by
classificacao_principal.posicao asc
) as t1
group by
id_equipa
go
This solution does not take care about ties.
AMB
"Jorge Ferreira" wrote:
> Hello everyone,
> I'm making a query that is supposed to sum the 3 best results.
> The query is this:
> SELECT top 3 classificacao_principal.id_equipa AS equipa, SUM(classificacao_principal.posicao) AS soma_pontos
> FROM classificacao_principal
> INNER JOIN atleta ON classificacao_principal.dorsal = atleta.dorsal_atl
> WHERE (atleta.sexo_atl = 'F' AND classificacao_principal.id_equipa='46')
> GROUP BY classificacao_principal.id_equipa
> ORDER BY soma_pontos ASC
> This query resturns me the top 3 results, but the SUM is made with all the results available. What i want is to sum only the 3 best results.
> Any ideas?|||Your ORDER BY soma_pontos applying the TOP cluase to the
SUM(classificacao_principal.posicao) not to
classificacao_principal.id_equipa
Try removing the Order by and see if it works.
--
Thanks
Ravi
"Jorge Ferreira" wrote:
> Hello everyone,
> I'm making a query that is supposed to sum the 3 best results.
> The query is this:
> SELECT top 3 classificacao_principal.id_equipa AS equipa, SUM(classificacao_principal.posicao) AS soma_pontos
> FROM classificacao_principal
> INNER JOIN atleta ON classificacao_principal.dorsal = atleta.dorsal_atl
> WHERE (atleta.sexo_atl = 'F' AND classificacao_principal.id_equipa='46')
> GROUP BY classificacao_principal.id_equipa
> ORDER BY soma_pontos ASC
> This query resturns me the top 3 results, but the SUM is made with all the results available. What i want is to sum only the 3 best results.
> Any ideas?|||That´s it.
Thank you for your answer.
Jorge
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> escreveu na
mensagem news:3D7AF53E-0A69-47AE-B66A-62E07C4B3EE5@.microsoft.com...
> Try,
> select
> id_equipa,
> sum(posicao) as soma_pontos
> from
> (
> SELECT top 3
> classificacao_principal.id_equipa AS equipa,
> classificacao_principal.posicao
> FROM
> classificacao_principal
> INNER JOIN
> atleta
> ON classificacao_principal.dorsal = atleta.dorsal_atl
> WHERE
> atleta.sexo_atl = 'F'
> AND classificacao_principal.id_equipa='46'
> order by
> classificacao_principal.posicao asc
> ) as t1
> group by
> id_equipa
> go
> This solution does not take care about ties.
>
> AMB
> "Jorge Ferreira" wrote:
>> Hello everyone,
>> I'm making a query that is supposed to sum the 3 best results.
>> The query is this:
>> SELECT top 3 classificacao_principal.id_equipa AS equipa,
>> SUM(classificacao_principal.posicao) AS soma_pontos
>> FROM classificacao_principal
>> INNER JOIN atleta ON classificacao_principal.dorsal = atleta.dorsal_atl
>> WHERE (atleta.sexo_atl = 'F' AND classificacao_principal.id_equipa='46')
>> GROUP BY classificacao_principal.id_equipa
>> ORDER BY soma_pontos ASC
>> This query resturns me the top 3 results, but the SUM is made with all
>> the results available. What i want is to sum only the 3 best results.
>> Any ideas?
>

query syntax issue

This is a multi-part message in MIME format.
--=_NextPart_000_0008_01C621B5.E56A3D60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
What is the difference between
use [aes50] DBCC SHRINKFILE (N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE ('aes50_Log', TRUNCATEONLY)
What does the N represent?
--=_NextPart_000_0008_01C621B5.E56A3D60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
What is the difference between
use [aes50] DBCC SHRINKFILE = (N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE ('aes50_Log', = TRUNCATEONLY)
What does the N represent?
--=_NextPart_000_0008_01C621B5.E56A3D60--This is a multi-part message in MIME format.
--=_NextPart_000_000B_01C621B6.39640AE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
UNICODE
"Mark" <mark_kurten@.acordia.com> wrote in message =news:O7voP$dIGHA.3492@.TK2MSFTNGP09.phx.gbl...
What is the difference between
use [aes50] DBCC SHRINKFILE (N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE ('aes50_Log', TRUNCATEONLY)
What does the N represent?
--=_NextPart_000_000B_01C621B6.39640AE0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

UNICODE
"Mark" =wrote in message news:O7voP$dIGHA.3492=@.TK2MSFTNGP09.phx.gbl...
What is the difference =between
use [aes50] DBCC SHRINKFILE =(N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE =('aes50_Log', TRUNCATEONLY)
What does the N =represent?

---

--=_NextPart_000_000B_01C621B6.39640AE0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0015_01C621B6.BB0ACED0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
From BOL
Unicode strings
Unicode strings have a format similar to character strings but are =preceded by an N identifier (N stands for National Language in the =SQL-92 standard). The N prefix must be uppercase. For example, ='Mich=E9l' is a character constant while N'Mich=E9l' is a Unicode =constant. Unicode constants are interpreted as Unicode data, and are not =evaluated using a code page. Unicode constants do have a collation, =which primarily controls comparisons and case sensitivity. Unicode =constants are assigned the default collation of the current database, =unless the COLLATE clause is used to specify a collation. Unicode data =is stored using two bytes per character, as opposed to one byte per =character for character data. For more information, see Using Unicode =Data.
Unicode string constants support enhanced collations.
"Mark" <mark_kurten@.acordia.com> wrote in message =news:O7voP$dIGHA.3492@.TK2MSFTNGP09.phx.gbl...
What is the difference between
use [aes50] DBCC SHRINKFILE (N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE ('aes50_Log', TRUNCATEONLY)
What does the N represent?
--=_NextPart_000_0015_01C621B6.BB0ACED0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

From BOL
Unicode strings
Unicode strings have a format similar to character =strings but are preceded by an N identifier (N stands for National Language in the =SQL-92 standard). The N prefix must be uppercase. For example, 'Mich=E9l' is a =character constant while N'Mich=E9l' is a Unicode constant. Unicode constants are interpreted as Unicode data, and are not evaluated using a code page. =Unicode constants do have a collation, which primarily controls comparisons and =case sensitivity. Unicode constants are assigned the default collation of the =current database, unless the COLLATE clause is used to specify a collation. =Unicode data is stored using two bytes per character, as opposed to one byte per =character for character data. For more information, see Using Unicode Data.
Unicode string constants support enhanced =collations.
"Mark" =wrote in message news:O7voP$dIGHA.3492=@.TK2MSFTNGP09.phx.gbl...
What is the difference =between
use [aes50] DBCC SHRINKFILE =(N'aes50_Log', TRUNCATEONLY)
And
use [aes50] DBCC SHRINKFILE =('aes50_Log', TRUNCATEONLY)
What does the N =represent?

---

--=_NextPart_000_0015_01C621B6.BB0ACED0--