Showing posts with label matrix. Show all posts
Showing posts with label matrix. Show all posts

Monday, March 26, 2012

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.

Monday, February 20, 2012

Query String using Web Matrix

Hi folks,

I have two tables in one database.

One table has an automatic numbering primary key named ID and is named rfi.

The other table has a field named rfinumber and is named discussion.

Both ID and rfinumber have a datatype of number.

Upon using the querybuilder with Web Matrix, I issue a select command to get all records from the discussion table that have a rfinumber field equal to the ID field in the rfi table.

Problem is that I am getting the entire discussion table when I use the following query:

"SELECT [discussion].* FROM [discussion], [rfi] WHERE ([discussion].[rfinumber] = [rfi].[ID])"

For example

DISCUSSION TABLE
rfinumber
1
1
1
2

RFI TABLE
rfi
1
2

Given the query, I should get a discussion table only listing the rfinumber = 1.

When I run the query from my database package it runs fine??

Any clues?

thanks,
glenn

gmeadows:

Problem is that I am getting the entire discussion table when I use the following query:


That's exactly what I would expect. You are returning a row from discussion matched with each row from rfi where the rfinumber from discussion is the same as the ID from rfi. Why would you expect it only to return those rows where rfinumber = 1? You need to use a parameter of some sort to make that happen.