I have a table that has different items with different states


eg. Spec ID       |    SpecType        |    state |

   1            Whole Blood      9   
   2            Whole Blood      11
   3            Whole Blood      6
   4            Whole Blood      11
   5            Whole Blood      11
   6            urine         11
   7            urine         9

I would like to create a query (to produce a report) that would group by Spec Type and give me a state count
even if a certain state does not exists (return 0 columns also) for any given spec type

eg.

   SpecType    |    Count_state(6) | Count_state(9) | Count_state(11)
   Whole Blood   1         1      4
   urine      0         1      1



Thanks for the help

silven <silven@canada.com> wrote:

I have a table that has different items with different states


eg. Spec ID       |    SpecType        |    state |

   1            Whole Blood      9   
   2            Whole Blood      11
   3            Whole Blood      6
   4            Whole Blood      11
   5            Whole Blood      11
   6            urine         11
   7            urine         9

I would like to create a query (to produce a report) that would group by Spec Type and give me a state count
even if a certain state does not exists (return 0 columns also) for any given spec type


small correction
eg.

   SpecType    |    Count_state(6) | Count_state(9) | Count_state(11)
   Whole Blood   1         1      3
   urine      0         1      1



Thanks for the help

"silven" <silven@canada.com> wrote in message
news:086607D9-5C3B-420F-BA5A-99296A125064@news.elevatesoft.com...
> silven <silven@canada.com> wrote:
>
> I have a table that has different items with different states
>

If you have a known and finite number of states (that is, if you know what
columns you want), it is fairly simple. Use sum instead of count, as follows

select SpecID, sum(if(state = 1 then 1 else 0)) State1, sum(if(state = 2
then 1 else 0)) State2 ...
group by SpecID

Otherwise, it gets more complicated, both to to extract the data and to
produce a report.

Robert

>
> eg. Spec ID | SpecType     | state |
>
>    1 Whole Blood 9
>    2 Whole Blood 11
>    3 Whole Blood 6
>    4 Whole Blood 11
>    5 Whole Blood 11
>    6 urine 11
>    7 urine 9
>
> I would like to create a query (to produce a report) that would group by
> Spec Type and give me a state count
> even if a certain state does not exists (return 0 columns also) for any
> given spec type
>
>
> small correction
> eg.
>
> SpecType    | Count_state(6) | Count_state(9) | Count_state(11)
> Whole Blood 1 1 3
> urine 0 1 1
>
>
>
> Thanks for the help
>

Thanks,
Definitely a lot easier than the solution I had...

is there a way of summing the 2 different state sums in such a  query


select SpecType, sum(if(state = 9 then 1 else 0)) ReAnalyzed, sum(if(state = 11 then 1 else 0)) as SumSent,sum(if(state = 3 then 1 else 0)) as SumPassed, SumSent + SumPassed as
SumOf2StateSums

From Specs
group by SpecType


"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote:


"silven" <silven@canada.com> wrote in message
news:086607D9-5C3B-420F-BA5A-99296A125064@news.elevatesoft.com...
> silven <silven@canada.com> wrote:
>
> I have a table that has different items with different states
>

If you have a known and finite number of states (that is, if you know what
columns you want), it is fairly simple. Use sum instead of count, as follows

select SpecID, sum(if(state = 1 then 1 else 0)) State1, sum(if(state = 2
then 1 else 0)) State2 ...
group by SpecID

Otherwise, it gets more complicated, both to to extract the data and to
produce a report.

Robert

>
> eg. Spec ID | SpecType     | state |
>
>    1 Whole Blood 9
>    2 Whole Blood 11
>    3 Whole Blood 6
>    4 Whole Blood 11
>    5 Whole Blood 11
>    6 urine 11
>    7 urine 9
>
> I would like to create a query (to produce a report) that would group by
> Spec Type and give me a state count
> even if a certain state does not exists (return 0 columns also) for any
> given spec type
>
>
> small correction
> eg.
>
> SpecType    | Count_state(6) | Count_state(9) | Count_state(11)
> Whole Blood 1 1 3
> urine 0 1 1
>
>
>
> Thanks for the help
>

"silven" <silven@canada.com> wrote in message
news:3291C65F-CB75-445F-B1D5-AC1D68EE503C@news.elevatesoft.com...
> Thanks,
> Definitely a lot easier than the solution I had...
>
> is there a way of summing the 2 different state sums in such a  query
>

if state in (1,2) then 1 else 0 SumOf1and2

Robert

Thanks, Robert

"Robert" <ngsemail2005withoutthis@yahoo.com.ar> wrote:


"silven" <silven@canada.com> wrote in message
news:3291C65F-CB75-445F-B1D5-AC1D68EE503C@news.elevatesoft.com...
> Thanks,
> Definitely a lot easier than the solution I had...
>
> is there a way of summing the 2 different state sums in such a  query
>

if state in (1,2) then 1 else 0 SumOf1and2

Robert