Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 6 of 6 total |
Query help please |
Sun, Jul 1 2007 12:28 PM | Permanent Link |
silven | 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 |
Sun, Jul 1 2007 3:31 PM | Permanent Link |
silven | 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 |
Sun, Jul 1 2007 5:19 PM | Permanent Link |
"Robert" | "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 > |
Sun, Jul 1 2007 5:53 PM | Permanent Link |
silven | 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 > |
Sun, Jul 1 2007 6:30 PM | Permanent Link |
"Robert" | "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 |
Sun, Jul 1 2007 6:53 PM | Permanent Link |
silven | 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 |
This web page was last updated on Saturday, May 4, 2024 at 09:18 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |