Easy to see, hard to put in SQL

Easy to see, hard to put in SQL

Post by Paul Hasting » Sat, 01 Dec 2001 05:10:25



Hi -

I am trying to figure out a query that will take  a set of serial numbers
which can be at multiple levels and produce an output which summarizes the
highest serial numbers at each level. Any row which has data at that level
and no higher gets a count of one for that level. The summary provides a
count of the totals for each level.

This is hard to put into words (and for me SQL) but it is fairly easy to
see. Here is a sample table and the output I am looking for.

Input table

Level_0     Level_1    Level_2    Level_3
----------  ---------- ---------- ----------
1
2
3
4           hae1sn25
5           hae1sn24
6           hae1sn23
7           hae1sn22
8           hae1sn21
9           hae1sn17   hae2sn17
10          hae1sn16   hae2sn16
11          hae1sn13   hae2sn13   hae3sn13
12          hae1sn12   hae2sn12   hae3sn12
13          hae1sn11   hae2sn11   hae3sn11
14          hae1sn10   hae2sn10   hae3sn14

Output table (or set of columns)

Level_0     Level_1    Level_2    Level_3
----------  ---------- ---------- ----------
3           5          2          4

Can anyone help me past my confusion?

Thanks In Advance,

Paul Hastings
Principal Computer Scientist
Jorge Scientific Corporation
104 Park Drive
Warner Robins, GA 31088
Voice: (478) 923-2662
FAX: (478) 923-5625

 
 
 

Easy to see, hard to put in SQL

Post by oj » Sat, 01 Dec 2001 05:21:46


paul,

select lev3=count(level_3),
    lev2=count(level_2)-count(level_3),
    lev1=count(level_1)-count(level_2),
    lev0=count(*)-count(level_1)
from tb

-oj


> Hi -

> I am trying to figure out a query that will take  a set of serial numbers
> which can be at multiple levels and produce an output which summarizes the
> highest serial numbers at each level. Any row which has data at that level
> and no higher gets a count of one for that level. The summary provides a
> count of the totals for each level.

> This is hard to put into words (and for me SQL) but it is fairly easy to
> see. Here is a sample table and the output I am looking for.

> Input table

> Level_0     Level_1    Level_2    Level_3
> ----------  ---------- ---------- ----------
> 1
> 2
> 3
> 4           hae1sn25
> 5           hae1sn24
> 6           hae1sn23
> 7           hae1sn22
> 8           hae1sn21
> 9           hae1sn17   hae2sn17
> 10          hae1sn16   hae2sn16
> 11          hae1sn13   hae2sn13   hae3sn13
> 12          hae1sn12   hae2sn12   hae3sn12
> 13          hae1sn11   hae2sn11   hae3sn11
> 14          hae1sn10   hae2sn10   hae3sn14

> Output table (or set of columns)

> Level_0     Level_1    Level_2    Level_3
> ----------  ---------- ---------- ----------
> 3           5          2          4

> Can anyone help me past my confusion?

> Thanks In Advance,

> Paul Hastings
> Principal Computer Scientist
> Jorge Scientific Corporation
> 104 Park Drive
> Warner Robins, GA 31088
> Voice: (478) 923-2662
> FAX: (478) 923-5625



 
 
 

Easy to see, hard to put in SQL

Post by Rich Dillo » Sat, 01 Dec 2001 05:35:51


Paul,

SELECT
  COUNT(case when Level_1 is null then Level_0 end) L0,
  COUNT(case when Level_2 is null then Level_1 end) L1,
  COUNT(case when Level_3 is null then Level_2 end) L2,
  COUNT(Level_3) L3
FROM
  InputTable;

Hope that helps,
Rich


> Hi -

> I am trying to figure out a query that will take  a set of serial numbers
> which can be at multiple levels and produce an output which summarizes the
> highest serial numbers at each level. Any row which has data at that level
> and no higher gets a count of one for that level. The summary provides a
> count of the totals for each level.

> This is hard to put into words (and for me SQL) but it is fairly easy to
> see. Here is a sample table and the output I am looking for.

> Input table

> Level_0     Level_1    Level_2    Level_3
> ----------  ---------- ---------- ----------
> 1
> 2
> 3
> 4           hae1sn25
> 5           hae1sn24
> 6           hae1sn23
> 7           hae1sn22
> 8           hae1sn21
> 9           hae1sn17   hae2sn17
> 10          hae1sn16   hae2sn16
> 11          hae1sn13   hae2sn13   hae3sn13
> 12          hae1sn12   hae2sn12   hae3sn12
> 13          hae1sn11   hae2sn11   hae3sn11
> 14          hae1sn10   hae2sn10   hae3sn14

> Output table (or set of columns)

> Level_0     Level_1    Level_2    Level_3
> ----------  ---------- ---------- ----------
> 3           5          2          4

> Can anyone help me past my confusion?

> Thanks In Advance,

> Paul Hastings
> Principal Computer Scientist
> Jorge Scientific Corporation
> 104 Park Drive
> Warner Robins, GA 31088
> Voice: (478) 923-2662
> FAX: (478) 923-5625


 
 
 

Easy to see, hard to put in SQL

Post by Paul Hasting » Sat, 01 Dec 2001 05:59:59


Rich -

Thanks! Thats awesome! Thats works if the unused columns are truely null. I
have to deal with blanks too. Nulls and any number of blanks have the same
meaning. Do you know how to do that?

Thanks,

Paul


> Paul,

> SELECT
>   COUNT(case when Level_1 is null then Level_0 end) L0,
>   COUNT(case when Level_2 is null then Level_1 end) L1,
>   COUNT(case when Level_3 is null then Level_2 end) L2,
>   COUNT(Level_3) L3
> FROM
>   InputTable;

> Hope that helps,
> Rich



> > Hi -

> > I am trying to figure out a query that will take  a set of serial
numbers
> > which can be at multiple levels and produce an output which summarizes
the
> > highest serial numbers at each level. Any row which has data at that
level
> > and no higher gets a count of one for that level. The summary provides a
> > count of the totals for each level.

> > This is hard to put into words (and for me SQL) but it is fairly easy to
> > see. Here is a sample table and the output I am looking for.

> > Input table

> > Level_0     Level_1    Level_2    Level_3
> > ----------  ---------- ---------- ----------
> > 1
> > 2
> > 3
> > 4           hae1sn25
> > 5           hae1sn24
> > 6           hae1sn23
> > 7           hae1sn22
> > 8           hae1sn21
> > 9           hae1sn17   hae2sn17
> > 10          hae1sn16   hae2sn16
> > 11          hae1sn13   hae2sn13   hae3sn13
> > 12          hae1sn12   hae2sn12   hae3sn12
> > 13          hae1sn11   hae2sn11   hae3sn11
> > 14          hae1sn10   hae2sn10   hae3sn14

> > Output table (or set of columns)

> > Level_0     Level_1    Level_2    Level_3
> > ----------  ---------- ---------- ----------
> > 3           5          2          4

> > Can anyone help me past my confusion?

> > Thanks In Advance,

> > Paul Hastings
> > Principal Computer Scientist
> > Jorge Scientific Corporation
> > 104 Park Drive
> > Warner Robins, GA 31088
> > Voice: (478) 923-2662
> > FAX: (478) 923-5625


 
 
 

Easy to see, hard to put in SQL

Post by Steve Kas » Sat, 01 Dec 2001 06:17:07


Paul,

  If an entry at level i guarantees entries at the lower levels, this
will work:

select sum(case when level_1 is null then 1 else 0 end) as Level_0,
       sum(case when level_2 is null and level_1 is not null then 1 else 0
end) as Level_1,
       sum(case when level_3 is null and level_2 is not null then 1 else 0
end) as Level_2,
       sum(case when level_3 is not null then 1 else 0 end) as Level_3

Steve Kass
Drew University


> Hi -

> I am trying to figure out a query that will take  a set of serial numbers
> which can be at multiple levels and produce an output which summarizes the
> highest serial numbers at each level. Any row which has data at that level
> and no higher gets a count of one for that level. The summary provides a
> count of the totals for each level.

> This is hard to put into words (and for me SQL) but it is fairly easy to
> see. Here is a sample table and the output I am looking for.

> Input table

> Level_0     Level_1    Level_2    Level_3
> ----------  ---------- ---------- ----------
> 1
> 2
> 3
> 4           hae1sn25
> 5           hae1sn24
> 6           hae1sn23
> 7           hae1sn22
> 8           hae1sn21
> 9           hae1sn17   hae2sn17
> 10          hae1sn16   hae2sn16
> 11          hae1sn13   hae2sn13   hae3sn13
> 12          hae1sn12   hae2sn12   hae3sn12
> 13          hae1sn11   hae2sn11   hae3sn11
> 14          hae1sn10   hae2sn10   hae3sn14

> Output table (or set of columns)

> Level_0     Level_1    Level_2    Level_3
> ----------  ---------- ---------- ----------
> 3           5          2          4

> Can anyone help me past my confusion?

> Thanks In Advance,

> Paul Hastings
> Principal Computer Scientist
> Jorge Scientific Corporation
> 104 Park Drive
> Warner Robins, GA 31088
> Voice: (478) 923-2662
> FAX: (478) 923-5625


 
 
 

Easy to see, hard to put in SQL

Post by Tae Le » Sat, 01 Dec 2001 06:20:57


If you replace blanks with nothing, you can determine if there is data or
just multiple blanks with length of the results.

 SELECT
   COUNT(case when Level_1 is null or LEN(REPLACE(Level_1,' ','')) = 0 then
Level_0 end) L0,
   COUNT(case when Level_2 is null or LEN(REPLACE(Level_2,' ','')) = 0 then
Level_1 end) L1,
   COUNT(case when Level_3 is null or LEN(REPLACE(Level_3,' ','')) = 0 then
Level_2 end) L2,
   COUNT(Level_3) L3
 FROM
   InputTable;

Hope this helps.

-Tae Lee


> Rich -

> Thanks! Thats awesome! Thats works if the unused columns are truely null.
I
> have to deal with blanks too. Nulls and any number of blanks have the same
> meaning. Do you know how to do that?

> Thanks,

> Paul



> > Paul,

> > SELECT
> >   COUNT(case when Level_1 is null then Level_0 end) L0,
> >   COUNT(case when Level_2 is null then Level_1 end) L1,
> >   COUNT(case when Level_3 is null then Level_2 end) L2,
> >   COUNT(Level_3) L3
> > FROM
> >   InputTable;

> > Hope that helps,
> > Rich



> > > Hi -

> > > I am trying to figure out a query that will take  a set of serial
> numbers
> > > which can be at multiple levels and produce an output which summarizes
> the
> > > highest serial numbers at each level. Any row which has data at that
> level
> > > and no higher gets a count of one for that level. The summary provides
a
> > > count of the totals for each level.

> > > This is hard to put into words (and for me SQL) but it is fairly easy
to
> > > see. Here is a sample table and the output I am looking for.

> > > Input table

> > > Level_0     Level_1    Level_2    Level_3
> > > ----------  ---------- ---------- ----------
> > > 1
> > > 2
> > > 3
> > > 4           hae1sn25
> > > 5           hae1sn24
> > > 6           hae1sn23
> > > 7           hae1sn22
> > > 8           hae1sn21
> > > 9           hae1sn17   hae2sn17
> > > 10          hae1sn16   hae2sn16
> > > 11          hae1sn13   hae2sn13   hae3sn13
> > > 12          hae1sn12   hae2sn12   hae3sn12
> > > 13          hae1sn11   hae2sn11   hae3sn11
> > > 14          hae1sn10   hae2sn10   hae3sn14

> > > Output table (or set of columns)

> > > Level_0     Level_1    Level_2    Level_3
> > > ----------  ---------- ---------- ----------
> > > 3           5          2          4

> > > Can anyone help me past my confusion?

> > > Thanks In Advance,

> > > Paul Hastings
> > > Principal Computer Scientist
> > > Jorge Scientific Corporation
> > > 104 Park Drive
> > > Warner Robins, GA 31088
> > > Voice: (478) 923-2662
> > > FAX: (478) 923-5625


 
 
 

Easy to see, hard to put in SQL

Post by oj » Sat, 01 Dec 2001 06:22:12


doesn't account for empty/blanks... need the following as part of query...

or datalength(ltrim(rtrim(level_x)))=0

-oj


> Paul,

>   If an entry at level i guarantees entries at the lower levels, this
> will work:

> select sum(case when level_1 is null then 1 else 0 end) as Level_0,
>        sum(case when level_2 is null and level_1 is not null then 1 else 0
> end) as Level_1,
>        sum(case when level_3 is null and level_2 is not null then 1 else 0
> end) as Level_2,
>        sum(case when level_3 is not null then 1 else 0 end) as Level_3

> Steve Kass
> Drew University


> > Hi -

> > I am trying to figure out a query that will take  a set of serial
numbers
> > which can be at multiple levels and produce an output which summarizes
the
> > highest serial numbers at each level. Any row which has data at that
level
> > and no higher gets a count of one for that level. The summary provides a
> > count of the totals for each level.

> > This is hard to put into words (and for me SQL) but it is fairly easy to
> > see. Here is a sample table and the output I am looking for.

> > Input table

> > Level_0     Level_1    Level_2    Level_3
> > ----------  ---------- ---------- ----------
> > 1
> > 2
> > 3
> > 4           hae1sn25
> > 5           hae1sn24
> > 6           hae1sn23
> > 7           hae1sn22
> > 8           hae1sn21
> > 9           hae1sn17   hae2sn17
> > 10          hae1sn16   hae2sn16
> > 11          hae1sn13   hae2sn13   hae3sn13
> > 12          hae1sn12   hae2sn12   hae3sn12
> > 13          hae1sn11   hae2sn11   hae3sn11
> > 14          hae1sn10   hae2sn10   hae3sn14

> > Output table (or set of columns)

> > Level_0     Level_1    Level_2    Level_3
> > ----------  ---------- ---------- ----------
> > 3           5          2          4

> > Can anyone help me past my confusion?

> > Thanks In Advance,

> > Paul Hastings
> > Principal Computer Scientist
> > Jorge Scientific Corporation
> > 104 Park Drive
> > Warner Robins, GA 31088
> > Voice: (478) 923-2662
> > FAX: (478) 923-5625


 
 
 

Easy to see, hard to put in SQL

Post by Rich Dillo » Sat, 01 Dec 2001 06:30:27


Same Idea...

...  case when Level_1 is null or Level_1 = '' then ...

or

... case when nullif(Level_1,'') is null then ...

oj's statement can also be modified easily as well...

SELECT
  count(nullif(Level_1,''))-count(*) AS L0,
  count(nullif(Level_2,''))-count(nullif(Level_1,'')) AS L1,
  count(nullif(Level_3,''))-count(nullif(Level_2,'')) AS L2,
  count(nullif(Level_4,'')) AS L3
FROM
  InputTable;

SQL Server trims strings of pure spaces. So the "any number of blanks" part
shouldn't be a problem.  For example:

SELECT 'They are the same'
WHERE SPACE(1) = SPACE(5)


> Rich -

> Thanks! Thats awesome! Thats works if the unused columns are truely null.
I
> have to deal with blanks too. Nulls and any number of blanks have the same
> meaning. Do you know how to do that?

> Thanks,

> Paul



> > Paul,

> > SELECT
> >   COUNT(case when Level_1 is null then Level_0 end) L0,
> >   COUNT(case when Level_2 is null then Level_1 end) L1,
> >   COUNT(case when Level_3 is null then Level_2 end) L2,
> >   COUNT(Level_3) L3
> > FROM
> >   InputTable;

> > Hope that helps,
> > Rich



> > > Hi -

> > > I am trying to figure out a query that will take  a set of serial
> numbers
> > > which can be at multiple levels and produce an output which summarizes
> the
> > > highest serial numbers at each level. Any row which has data at that
> level
> > > and no higher gets a count of one for that level. The summary provides
a
> > > count of the totals for each level.

> > > This is hard to put into words (and for me SQL) but it is fairly easy
to
> > > see. Here is a sample table and the output I am looking for.

> > > Input table

> > > Level_0     Level_1    Level_2    Level_3
> > > ----------  ---------- ---------- ----------
> > > 1
> > > 2
> > > 3
> > > 4           hae1sn25
> > > 5           hae1sn24
> > > 6           hae1sn23
> > > 7           hae1sn22
> > > 8           hae1sn21
> > > 9           hae1sn17   hae2sn17
> > > 10          hae1sn16   hae2sn16
> > > 11          hae1sn13   hae2sn13   hae3sn13
> > > 12          hae1sn12   hae2sn12   hae3sn12
> > > 13          hae1sn11   hae2sn11   hae3sn11
> > > 14          hae1sn10   hae2sn10   hae3sn14

> > > Output table (or set of columns)

> > > Level_0     Level_1    Level_2    Level_3
> > > ----------  ---------- ---------- ----------
> > > 3           5          2          4

> > > Can anyone help me past my confusion?

> > > Thanks In Advance,

> > > Paul Hastings
> > > Principal Computer Scientist
> > > Jorge Scientific Corporation
> > > 104 Park Drive
> > > Warner Robins, GA 31088
> > > Voice: (478) 923-2662
> > > FAX: (478) 923-5625


 
 
 

Easy to see, hard to put in SQL

Post by Paul Hasting » Sat, 01 Dec 2001 06:33:54


Wow!

Thanks everyone. I was really beating my head against the wall. I have got
it now!

Paul


> Hi -

> I am trying to figure out a query that will take  a set of serial numbers
> which can be at multiple levels and produce an output which summarizes the
> highest serial numbers at each level. Any row which has data at that level
> and no higher gets a count of one for that level. The summary provides a
> count of the totals for each level.

> This is hard to put into words (and for me SQL) but it is fairly easy to
> see. Here is a sample table and the output I am looking for.

> Input table

> Level_0     Level_1    Level_2    Level_3
> ----------  ---------- ---------- ----------
> 1
> 2
> 3
> 4           hae1sn25
> 5           hae1sn24
> 6           hae1sn23
> 7           hae1sn22
> 8           hae1sn21
> 9           hae1sn17   hae2sn17
> 10          hae1sn16   hae2sn16
> 11          hae1sn13   hae2sn13   hae3sn13
> 12          hae1sn12   hae2sn12   hae3sn12
> 13          hae1sn11   hae2sn11   hae3sn11
> 14          hae1sn10   hae2sn10   hae3sn14

> Output table (or set of columns)

> Level_0     Level_1    Level_2    Level_3
> ----------  ---------- ---------- ----------
> 3           5          2          4

> Can anyone help me past my confusion?

> Thanks In Advance,

> Paul Hastings
> Principal Computer Scientist
> Jorge Scientific Corporation
> 104 Park Drive
> Warner Robins, GA 31088
> Voice: (478) 923-2662
> FAX: (478) 923-5625


 
 
 

Easy to see, hard to put in SQL

Post by Steve Kas » Sat, 01 Dec 2001 06:43:16


Rich,

  Technically, SPACE(1) and SPACE(5) are not the same.  What's going
on is that regardless of the collation you are using, the = operator will
return true when the strings being compared differ only by trailing spaces.
The = operator when used on string types isn't a test for equality.

  If they were the same, then SPACE(1)+'A' and SPACE(5)+'A' would
be the same, and they're not.  It's not an issue here, but it is worth being
aware of - it bites when you find out that len(s) + len(t) isn't
always len(s+t).

Steve Kass
Drew University


> Same Idea...

> ...  case when Level_1 is null or Level_1 = '' then ...

> or

> ... case when nullif(Level_1,'') is null then ...

> oj's statement can also be modified easily as well...

> SELECT
>   count(nullif(Level_1,''))-count(*) AS L0,
>   count(nullif(Level_2,''))-count(nullif(Level_1,'')) AS L1,
>   count(nullif(Level_3,''))-count(nullif(Level_2,'')) AS L2,
>   count(nullif(Level_4,'')) AS L3
> FROM
>   InputTable;

> SQL Server trims strings of pure spaces. So the "any number of blanks" part
> shouldn't be a problem.  For example:

> SELECT 'They are the same'
> WHERE SPACE(1) = SPACE(5)



> > Rich -

> > Thanks! Thats awesome! Thats works if the unused columns are truely null.
> I
> > have to deal with blanks too. Nulls and any number of blanks have the same
> > meaning. Do you know how to do that?

> > Thanks,

> > Paul



> > > Paul,

> > > SELECT
> > >   COUNT(case when Level_1 is null then Level_0 end) L0,
> > >   COUNT(case when Level_2 is null then Level_1 end) L1,
> > >   COUNT(case when Level_3 is null then Level_2 end) L2,
> > >   COUNT(Level_3) L3
> > > FROM
> > >   InputTable;

> > > Hope that helps,
> > > Rich



> > > > Hi -

> > > > I am trying to figure out a query that will take  a set of serial
> > numbers
> > > > which can be at multiple levels and produce an output which summarizes
> > the
> > > > highest serial numbers at each level. Any row which has data at that
> > level
> > > > and no higher gets a count of one for that level. The summary provides
> a
> > > > count of the totals for each level.

> > > > This is hard to put into words (and for me SQL) but it is fairly easy
> to
> > > > see. Here is a sample table and the output I am looking for.

> > > > Input table

> > > > Level_0     Level_1    Level_2    Level_3
> > > > ----------  ---------- ---------- ----------
> > > > 1
> > > > 2
> > > > 3
> > > > 4           hae1sn25
> > > > 5           hae1sn24
> > > > 6           hae1sn23
> > > > 7           hae1sn22
> > > > 8           hae1sn21
> > > > 9           hae1sn17   hae2sn17
> > > > 10          hae1sn16   hae2sn16
> > > > 11          hae1sn13   hae2sn13   hae3sn13
> > > > 12          hae1sn12   hae2sn12   hae3sn12
> > > > 13          hae1sn11   hae2sn11   hae3sn11
> > > > 14          hae1sn10   hae2sn10   hae3sn14

> > > > Output table (or set of columns)

> > > > Level_0     Level_1    Level_2    Level_3
> > > > ----------  ---------- ---------- ----------
> > > > 3           5          2          4

> > > > Can anyone help me past my confusion?

> > > > Thanks In Advance,

> > > > Paul Hastings
> > > > Principal Computer Scientist
> > > > Jorge Scientific Corporation
> > > > 104 Park Drive
> > > > Warner Robins, GA 31088
> > > > Voice: (478) 923-2662
> > > > FAX: (478) 923-5625


 
 
 

Easy to see, hard to put in SQL

Post by oj » Sat, 01 Dec 2001 07:01:48


nice one steve. can't be said better.

len() disregards trailing blanks; datalength() does.

-oj


> Rich,

>   Technically, SPACE(1) and SPACE(5) are not the same.  What's going
> on is that regardless of the collation you are using, the = operator will
> return true when the strings being compared differ only by trailing
spaces.
> The = operator when used on string types isn't a test for equality.

>   If they were the same, then SPACE(1)+'A' and SPACE(5)+'A' would
> be the same, and they're not.  It's not an issue here, but it is worth
being
> aware of - it bites when you find out that len(s) + len(t) isn't
> always len(s+t).

> Steve Kass
> Drew University


> > Same Idea...

> > ...  case when Level_1 is null or Level_1 = '' then ...

> > or

> > ... case when nullif(Level_1,'') is null then ...

> > oj's statement can also be modified easily as well...

> > SELECT
> >   count(nullif(Level_1,''))-count(*) AS L0,
> >   count(nullif(Level_2,''))-count(nullif(Level_1,'')) AS L1,
> >   count(nullif(Level_3,''))-count(nullif(Level_2,'')) AS L2,
> >   count(nullif(Level_4,'')) AS L3
> > FROM
> >   InputTable;

> > SQL Server trims strings of pure spaces. So the "any number of blanks"
part
> > shouldn't be a problem.  For example:

> > SELECT 'They are the same'
> > WHERE SPACE(1) = SPACE(5)



> > > Rich -

> > > Thanks! Thats awesome! Thats works if the unused columns are truely
null.
> > I
> > > have to deal with blanks too. Nulls and any number of blanks have the
same
> > > meaning. Do you know how to do that?

> > > Thanks,

> > > Paul



> > > > Paul,

> > > > SELECT
> > > >   COUNT(case when Level_1 is null then Level_0 end) L0,
> > > >   COUNT(case when Level_2 is null then Level_1 end) L1,
> > > >   COUNT(case when Level_3 is null then Level_2 end) L2,
> > > >   COUNT(Level_3) L3
> > > > FROM
> > > >   InputTable;

> > > > Hope that helps,
> > > > Rich



> > > > > Hi -

> > > > > I am trying to figure out a query that will take  a set of serial
> > > numbers
> > > > > which can be at multiple levels and produce an output which
summarizes
> > > the
> > > > > highest serial numbers at each level. Any row which has data at
that
> > > level
> > > > > and no higher gets a count of one for that level. The summary
provides
> > a
> > > > > count of the totals for each level.

> > > > > This is hard to put into words (and for me SQL) but it is fairly
easy
> > to
> > > > > see. Here is a sample table and the output I am looking for.

> > > > > Input table

> > > > > Level_0     Level_1    Level_2    Level_3
> > > > > ----------  ---------- ---------- ----------
> > > > > 1
> > > > > 2
> > > > > 3
> > > > > 4           hae1sn25
> > > > > 5           hae1sn24
> > > > > 6           hae1sn23
> > > > > 7           hae1sn22
> > > > > 8           hae1sn21
> > > > > 9           hae1sn17   hae2sn17
> > > > > 10          hae1sn16   hae2sn16
> > > > > 11          hae1sn13   hae2sn13   hae3sn13
> > > > > 12          hae1sn12   hae2sn12   hae3sn12
> > > > > 13          hae1sn11   hae2sn11   hae3sn11
> > > > > 14          hae1sn10   hae2sn10   hae3sn14

> > > > > Output table (or set of columns)

> > > > > Level_0     Level_1    Level_2    Level_3
> > > > > ----------  ---------- ---------- ----------
> > > > > 3           5          2          4

> > > > > Can anyone help me past my confusion?

> > > > > Thanks In Advance,

> > > > > Paul Hastings
> > > > > Principal Computer Scientist
> > > > > Jorge Scientific Corporation
> > > > > 104 Park Drive
> > > > > Warner Robins, GA 31088
> > > > > Voice: (478) 923-2662
> > > > > FAX: (478) 923-5625


 
 
 

Easy to see, hard to put in SQL

Post by Umachandar Jayachandra » Sat, 01 Dec 2001 13:48:56


    This will handle blanks easily.

SELECT COUNT( CASE WHEN NULLIF( l.Level_1, '' ) IS NULL THEN 1 END ) AS
Level_0,
       COUNT( CASE WHEN NULLIF( l.Level_2, '' ) IS NULL THEN 1 END ) AS
Level_1,
       COUNT( CASE WHEN NULLIF( l.Level_3, '' ) IS NULL THEN 1 END ) AS
Level_2,
       COUNT( NULLIF( l.Level_3, '' ) ) AS Level_3
  FROM tbl AS l

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

 
 
 

1. Easy to see, hard to put in SQL

Hi -

I am trying to figure out a query that will take  a set of serial numbers
which can be at multiple levels and produce an output which summarizes the
highest serial numbers at each level. Any row which has data at that level
and no higher gets a count of one for that level. The summary provides a
count of the totals for each level.

This is hard to put into words (and for me SQL) but it is fairly easy to
see. Here is a sample table and the output I am looking for.

Input table

Level_0     Level_1    Level_2    Level_3
----------  ---------- ---------- ----------
1
2
3
4           hae1sn25
5           hae1sn24
6           hae1sn23
7           hae1sn22
8           hae1sn21
9           hae1sn17   hae2sn17
10          hae1sn16   hae2sn16
11          hae1sn13   hae2sn13   hae3sn13
12          hae1sn12   hae2sn12   hae3sn12
13          hae1sn11   hae2sn11   hae3sn11
14          hae1sn10   hae2sn10   hae3sn14

Output table (or set of columns)

Level_0     Level_1    Level_2    Level_3
----------  ---------- ---------- ----------
3           5          2          4

Can anyone help me past my confusion?

Thanks In Advance,

Paul Hastings
Principal Computer Scientist
Jorge Scientific Corporation
104 Park Drive
Warner Robins, GA 31088
Voice: (478) 923-2662
FAX: (478) 923-5625

2. Seeing table names in DBACCESS

3. What is easiest way to put Access database to web, is it ASP+SQL+Javascript

4. How to stop queries processing

5. 1 very hard and 1 very Easy question

6. New to SQL Server

7. query help, hard for me, I hope easy for you

8. Two questions... one easy, one hard

9. FoxPro questions, 2 easy, 1 harder.. : )

10. Prolly easy question: putting values in listbox

11. sql*loader puts in in, how do I put it out

12. Client on 1 hard drive and server on other hard drive