Problem with query, trying to pull different date from the same field

26 Aug 2005 1:57 PM

hugo.flores

Hi,

I have a table of tests, and I want to be able to get the pass and fail
totals on the tests. Here's what I have so far

select month(lastmodified),datename(mm,(lastmodified))as month,
count(id_status) as Total, id_status
from tests
where id_status = 2 or id_status = 3
group by month(lastmodified),datename(mm,(lastmodified)), id_status
order by month(lastmodified)

This query would return something like this

MonthNumber MonthName                      Total       id_status
----------- ------------------------------ ----------- ---------
7           July                           5           2
7           July                           2           3
8           August                         2           2
8           August                         1           3

Right now, it brings me two rows, for each month, with the first row
being the tests passed on a month (id_status = 2) and the second row,
the tests failed.

I want to know if it's possible to have one row, for each month, with
the pass and fail totals in the same row.

Any help would be appreciated.

26 Aug 2005 2:18 PM

Alejandro Mesa

Try,

select
   month(lastmodified),
   datename(mm,(lastmodified))as month,
   sum(case when id_status = 2 then 1 else 0 end) as Total_2,
   sum(case when id_status = 3 then 1 else 0 end) as Total_3,
from
   tests
where
   id_status = 2 or id_status = 3
group by
   month(lastmodified),
   datename(mm,(lastmodified))
order by
   month(lastmodified)
go

AMB

Show quote

"hugo.flo***@ge.com" wrote:

> Hi,
>
> I have a table of tests, and I want to be able to get the pass and fail
> totals on the tests. Here's what I have so far
>
> select month(lastmodified),datename(mm,(lastmodified))as month,
> count(id_status) as Total, id_status
> from tests
> where id_status = 2 or id_status = 3
> group by month(lastmodified),datename(mm,(lastmodified)), id_status
> order by month(lastmodified)
>
> This query would return something like this
>
> MonthNumber MonthName                      Total       id_status
> ----------- ------------------------------ ----------- ---------
> 7           July                           5           2
> 7           July                           2           3
> 8           August                         2           2
> 8           August                         1           3
>
> Right now, it brings me two rows, for each month, with the first row
> being the tests passed on a month (id_status = 2) and the second row,
> the tests failed.
>
> I want to know if it's possible to have one row, for each month, with
> the pass and fail totals in the same row.
>
> Any help would be appreciated.
>
>

26 Aug 2005 2:54 PM

Hugo Flores

Thank you very much, this is exaclty what I needed