Welcome to dbFreaks.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

Simple Table but having query issues

  
   Database Help (Home) -> Programming RSS
Next:  Very slow replaying with profiler (2008)  
Author Message
John Wright

External


Since: Sep 29, 2008
Posts: 6



(Msg. 1) Posted: Fri Oct 23, 2009 12:41 pm
Post subject: Simple Table but having query issues
Archived from groups: microsoft>public>sqlserver>programming (more info?)
I have the following table with two columns

INGOT_SEQ nvarchar(4)
PITTING_MIN smallINT


So here is three queries I can run to get three seperate result

select INGOT_SEQ, count(PITTING_MIN)
from tubedb
where ingot_seq between '4000' and '5000'
and pitting_min > 0
and pitting_min between 1 and 79
group by ingot_seq
order by ingot_seq

select INGOT_SEQ, count(PITTING_MIN)
from tubedb
where ingot_seq between '4000' and '5000'
and pitting_min > 0
and pitting_min between 80 and 90
group by ingot_seq
order by ingot_seq

select INGOT_SEQ, count(PITTING_MIN)
from tubedb
where ingot_seq between '4000' and '5000'
and pitting_min > 90
group by ingot_seq
order by ingot_seq


I would like to get these into one row for each INGOT_SEQ and if there is a
null value, replace it with a zero. So the output would go from this

4010,5
4010,6
4010,115

to this

4010,5,6,115

I think this is a subquery or self join, but I can't get the syntax right.
Please help.

 >> Stay informed about: Simple Table but having query issues 
Back to top
Login to vote
--CELKO--

External


Since: Jan 11, 2008
Posts: 1089



(Msg. 2) Posted: Fri Oct 23, 2009 1:44 pm
Post subject: Re: Simple Table but having query issues [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
SELECT ingot_seq,
SUM (CASE WHEN pitting_min BETWEEN 1 AND 79),
AS foobar_1,

SUM (CASE WHEN pitting_min BETWEEN 80 AND 90),
AS foobar_2,

SUM (CASE WHEN pitting_min > 90)
AS foobar_3
FROM Tube_DB /* terrible table name! */
WHERE ingot_seq BETWEEN '4000' AND '5000'
GROUP BY ingot_seq;

 >> Stay informed about: Simple Table but having query issues 
Back to top
Login to vote
Tom Cooper

External


Since: Mar 12, 2004
Posts: 8



(Msg. 3) Posted: Fri Oct 23, 2009 4:35 pm
Post subject: Re: Simple Table but having query issues [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
select INGOT_SEQ, sum(Case When PITTING_MIN between 1 and 79 Then 1 Else 0),
sum(Case When PITTING_MIN between 80 and 90 Then 1 Else 0),
sum(Case When PITTING_MIN > 90 Then 1 Else 0),from tubedb
where ingot_seq between '4000' and '5000'
and pitting_min > 0
group by ingot_seq
order by ingot_seq;

Tom

"John Wright" wrote in message

>I have the following table with two columns
>
> INGOT_SEQ nvarchar(4)
> PITTING_MIN smallINT
>
>
> So here is three queries I can run to get three seperate result
>
> select INGOT_SEQ, count(PITTING_MIN)
> from tubedb
> where ingot_seq between '4000' and '5000'
> and pitting_min > 0
> and pitting_min between 1 and 79
> group by ingot_seq
> order by ingot_seq
>
> select INGOT_SEQ, count(PITTING_MIN)
> from tubedb
> where ingot_seq between '4000' and '5000'
> and pitting_min > 0
> and pitting_min between 80 and 90
> group by ingot_seq
> order by ingot_seq
>
> select INGOT_SEQ, count(PITTING_MIN)
> from tubedb
> where ingot_seq between '4000' and '5000'
> and pitting_min > 90
> group by ingot_seq
> order by ingot_seq
>
>
> I would like to get these into one row for each INGOT_SEQ and if there is
> a
> null value, replace it with a zero. So the output would go from this
>
> 4010,5
> 4010,6
> 4010,115
>
> to this
>
> 4010,5,6,115
>
> I think this is a subquery or self join, but I can't get the syntax right.
> Please help.
>
 >> Stay informed about: Simple Table but having query issues 
Back to top
Login to vote
John Bell

External


Since: Jun 01, 2004
Posts: 652



(Msg. 4) Posted: Fri Oct 23, 2009 5:25 pm
Post subject: Re: Simple Table but having query issues [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
"John Wright" wrote in message

>I have the following table with two columns
>
> INGOT_SEQ nvarchar(4)
> PITTING_MIN smallINT
>
>
> So here is three queries I can run to get three seperate result
>
> select INGOT_SEQ, count(PITTING_MIN)
> from tubedb
> where ingot_seq between '4000' and '5000'
> and pitting_min > 0
> and pitting_min between 1 and 79
> group by ingot_seq
> order by ingot_seq
>
> select INGOT_SEQ, count(PITTING_MIN)
> from tubedb
> where ingot_seq between '4000' and '5000'
> and pitting_min > 0
> and pitting_min between 80 and 90
> group by ingot_seq
> order by ingot_seq
>
> select INGOT_SEQ, count(PITTING_MIN)
> from tubedb
> where ingot_seq between '4000' and '5000'
> and pitting_min > 90
> group by ingot_seq
> order by ingot_seq
>
>
> I would like to get these into one row for each INGOT_SEQ and if there is
> a
> null value, replace it with a zero. So the output would go from this
>
> 4010,5
> 4010,6
> 4010,115
>
> to this
>
> 4010,5,6,115
>
> I think this is a subquery or self join, but I can't get the syntax right.
> Please help.
>
Hi

Try something like:

select INGOT_SEQ,
SUM(CASE WHEN pitting_min between 1 and 79 THEN 1 ELSE 0 END),
SUM(CASE WHEN pitting_min between 80 and 90 THEN 1 ELSE 0 END),
SUM(CASE WHEN and pitting_min > 90 THEN 1 ELSE 0 END)
count(PITTING_MIN)
from tubedb
where ingot_seq between '4000' and '5000'
and pitting_min > 0
group by ingot_seq
order by ingot_seq


John
 >> Stay informed about: Simple Table but having query issues 
Back to top
Login to vote
John Wright

External


Since: Sep 29, 2008
Posts: 6



(Msg. 5) Posted: Fri Oct 23, 2009 5:25 pm
Post subject: Re: Simple Table but having query issues [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Thanks one and all, I did find the solution using this syntax below, but I
hit a big, big snag. The database does not support ANSI 92 and the case
statement fails. Any idea how to do this without using case?

"John Bell" wrote:

>
> "John Wright" wrote in message
>
> >I have the following table with two columns
> >
> > INGOT_SEQ nvarchar(4)
> > PITTING_MIN smallINT
> >
> >
> > So here is three queries I can run to get three seperate result
> >
> > select INGOT_SEQ, count(PITTING_MIN)
> > from tubedb
> > where ingot_seq between '4000' and '5000'
> > and pitting_min > 0
> > and pitting_min between 1 and 79
> > group by ingot_seq
> > order by ingot_seq
> >
> > select INGOT_SEQ, count(PITTING_MIN)
> > from tubedb
> > where ingot_seq between '4000' and '5000'
> > and pitting_min > 0
> > and pitting_min between 80 and 90
> > group by ingot_seq
> > order by ingot_seq
> >
> > select INGOT_SEQ, count(PITTING_MIN)
> > from tubedb
> > where ingot_seq between '4000' and '5000'
> > and pitting_min > 90
> > group by ingot_seq
> > order by ingot_seq
> >
> >
> > I would like to get these into one row for each INGOT_SEQ and if there is
> > a
> > null value, replace it with a zero. So the output would go from this
> >
> > 4010,5
> > 4010,6
> > 4010,115
> >
> > to this
> >
> > 4010,5,6,115
> >
> > I think this is a subquery or self join, but I can't get the syntax right.
> > Please help.
> >
> Hi
>
> Try something like:
>
> select INGOT_SEQ,
> SUM(CASE WHEN pitting_min between 1 and 79 THEN 1 ELSE 0 END),
> SUM(CASE WHEN pitting_min between 80 and 90 THEN 1 ELSE 0 END),
> SUM(CASE WHEN and pitting_min > 90 THEN 1 ELSE 0 END)
> count(PITTING_MIN)
> from tubedb
> where ingot_seq between '4000' and '5000'
> and pitting_min > 0
> group by ingot_seq
> order by ingot_seq
>
>
> John
>
> .
>
 >> Stay informed about: Simple Table but having query issues 
Back to top
Login to vote
Jeroen Mostert

External


Since: Oct 08, 2009
Posts: 8



(Msg. 6) Posted: Fri Oct 23, 2009 6:25 pm
Post subject: Re: Simple Table but having query issues [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
John Wright wrote:
> Thanks one and all, I did find the solution using this syntax below, but I
> hit a big, big snag. The database does not support ANSI 92

Eh... You *are* using SQL Server, right? SQL Server has supported the CASE
expression since version 6.0, if I'm not mistaken.

If you're not using SQL Server, you're in the wrong group... Specifying what
RDBMS you're using tends to help a lot in generating usable solutions.

--
J.
 >> Stay informed about: Simple Table but having query issues 
Back to top
Login to vote
John Bell

External


Since: Jun 01, 2004
Posts: 652



(Msg. 7) Posted: Sat Oct 24, 2009 2:25 am
Post subject: Re: Simple Table but having query issues [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
You could try

SELECT INGOT_SEQ,SUM(SUM1), SUM(SUM2), SUM(SUM3), SUM(SUM4)
FROM (
select INGOT_SEQ,
1 AS SUM1,0 AS SUM2,0 AS SUM3 ,0 AS SUM4
from tubedb
where ingot_seq between '4000' and '5000'
and pitting_min > 0
AND pitting_min between 1 and 79
group by ingot_seq
UNION ALL
select INGOT_SEQ,
0,1,0,0
from tubedb
where ingot_seq between '4000' and '5000'
and pitting_min > 0
AND pitting_min between 80 and 90
group by ingot_seq
UNION ALL
select INGOT_SEQ,
0,0,1,0
from tubedb
where ingot_seq between '4000' and '5000'
and pitting_min > 0
AND pitting_min > 90
group by ingot_seq
UNION ALL
select INGOT_SEQ,
0,0,0,1
from tubedb
where ingot_seq between '4000' and '5000'
and pitting_min > 0
AND pitting_min > 90
group by ingot_seq
) AS A
order by ingot_seq

Or use view to do the union

John

"John Wright" wrote in message

> Thanks one and all, I did find the solution using this syntax below, but I
> hit a big, big snag. The database does not support ANSI 92 and the case
> statement fails. Any idea how to do this without using case?
>
> "John Bell" wrote:
>
>>
>> "John Wright" wrote in message
>>
>> >I have the following table with two columns
>> >
>> > INGOT_SEQ nvarchar(4)
>> > PITTING_MIN smallINT
>> >
>> >
>> > So here is three queries I can run to get three seperate result
>> >
>> > select INGOT_SEQ, count(PITTING_MIN)
>> > from tubedb
>> > where ingot_seq between '4000' and '5000'
>> > and pitting_min > 0
>> > and pitting_min between 1 and 79
>> > group by ingot_seq
>> > order by ingot_seq
>> >
>> > select INGOT_SEQ, count(PITTING_MIN)
>> > from tubedb
>> > where ingot_seq between '4000' and '5000'
>> > and pitting_min > 0
>> > and pitting_min between 80 and 90
>> > group by ingot_seq
>> > order by ingot_seq
>> >
>> > select INGOT_SEQ, count(PITTING_MIN)
>> > from tubedb
>> > where ingot_seq between '4000' and '5000'
>> > and pitting_min > 90
>> > group by ingot_seq
>> > order by ingot_seq
>> >
>> >
>> > I would like to get these into one row for each INGOT_SEQ and if there
>> > is
>> > a
>> > null value, replace it with a zero. So the output would go from this
>> >
>> > 4010,5
>> > 4010,6
>> > 4010,115
>> >
>> > to this
>> >
>> > 4010,5,6,115
>> >
>> > I think this is a subquery or self join, but I can't get the syntax
>> > right.
>> > Please help.
>> >
>> Hi
>>
>> Try something like:
>>
>> select INGOT_SEQ,
>> SUM(CASE WHEN pitting_min between 1 and 79 THEN 1 ELSE 0 END),
>> SUM(CASE WHEN pitting_min between 80 and 90 THEN 1 ELSE 0 END),
>> SUM(CASE WHEN and pitting_min > 90 THEN 1 ELSE 0 END)
>> count(PITTING_MIN)
>> from tubedb
>> where ingot_seq between '4000' and '5000'
>> and pitting_min > 0
>> group by ingot_seq
>> order by ingot_seq
>>
>>
>> John
>>
>> .
>>
 >> Stay informed about: Simple Table but having query issues 
Back to top
Login to vote
John Bell

External


Since: Jun 01, 2004
Posts: 652



(Msg. 8) Posted: Sat Oct 24, 2009 2:25 am
Post subject: Re: Simple Table but having query issues [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
That want' quite right it should have been

SELECT INGOT_SEQ,SUM(SUM1), SUM(SUM2), SUM(SUM3), SUM(SUM4)
FROM (
select INGOT_SEQ,
COUNT(*) AS SUM1,0 AS SUM2,0 AS SUM3 ,0 AS SUM4
from tubedb
where ingot_seq between '4000' and '5000'
and pitting_min > 0
AND pitting_min between 1 and 79
group by ingot_seq
....
) AS A
order by ingot_seq

Or

SELECT INGOT_SEQ,SUM(SUM1), SUM(SUM2), SUM(SUM3), SUM(SUM4)
FROM (
select INGOT_SEQ,
1 AS SUM1,0 AS SUM2,0 AS SUM3 ,0 AS SUM4
from tubedb
where ingot_seq between '4000' and '5000'
and pitting_min > 0
AND pitting_min between 1 and 79
....
) AS A
group by ingot_seq
order by ingot_seq

John

"John Bell" wrote in message

> You could try
>
> SELECT INGOT_SEQ,SUM(SUM1), SUM(SUM2), SUM(SUM3), SUM(SUM4)
> FROM (
> select INGOT_SEQ,
> 1 AS SUM1,0 AS SUM2,0 AS SUM3 ,0 AS SUM4
> from tubedb
> where ingot_seq between '4000' and '5000'
> and pitting_min > 0
> AND pitting_min between 1 and 79
> group by ingot_seq
> UNION ALL
> select INGOT_SEQ,
> 0,1,0,0
> from tubedb
> where ingot_seq between '4000' and '5000'
> and pitting_min > 0
> AND pitting_min between 80 and 90
> group by ingot_seq
> UNION ALL
> select INGOT_SEQ,
> 0,0,1,0
> from tubedb
> where ingot_seq between '4000' and '5000'
> and pitting_min > 0
> AND pitting_min > 90
> group by ingot_seq
> UNION ALL
> select INGOT_SEQ,
> 0,0,0,1
> from tubedb
> where ingot_seq between '4000' and '5000'
> and pitting_min > 0
> AND pitting_min > 90
> group by ingot_seq
> ) AS A
> order by ingot_seq
>
> Or use view to do the union
>
> John
>
> "John Wright" wrote in message
>
>> Thanks one and all, I did find the solution using this syntax below, but
>> I
>> hit a big, big snag. The database does not support ANSI 92 and the case
>> statement fails. Any idea how to do this without using case?
>>
>> "John Bell" wrote:
>>
>>>
>>> "John Wright" wrote in message
>>>
>>> >I have the following table with two columns
>>> >
>>> > INGOT_SEQ nvarchar(4)
>>> > PITTING_MIN smallINT
>>> >
>>> >
>>> > So here is three queries I can run to get three seperate result
>>> >
>>> > select INGOT_SEQ, count(PITTING_MIN)
>>> > from tubedb
>>> > where ingot_seq between '4000' and '5000'
>>> > and pitting_min > 0
>>> > and pitting_min between 1 and 79
>>> > group by ingot_seq
>>> > order by ingot_seq
>>> >
>>> > select INGOT_SEQ, count(PITTING_MIN)
>>> > from tubedb
>>> > where ingot_seq between '4000' and '5000'
>>> > and pitting_min > 0
>>> > and pitting_min between 80 and 90
>>> > group by ingot_seq
>>> > order by ingot_seq
>>> >
>>> > select INGOT_SEQ, count(PITTING_MIN)
>>> > from tubedb
>>> > where ingot_seq between '4000' and '5000'
>>> > and pitting_min > 90
>>> > group by ingot_seq
>>> > order by ingot_seq
>>> >
>>> >
>>> > I would like to get these into one row for each INGOT_SEQ and if there
>>> > is
>>> > a
>>> > null value, replace it with a zero. So the output would go from this
>>> >
>>> > 4010,5
>>> > 4010,6
>>> > 4010,115
>>> >
>>> > to this
>>> >
>>> > 4010,5,6,115
>>> >
>>> > I think this is a subquery or self join, but I can't get the syntax
>>> > right.
>>> > Please help.
>>> >
>>> Hi
>>>
>>> Try something like:
>>>
>>> select INGOT_SEQ,
>>> SUM(CASE WHEN pitting_min between 1 and 79 THEN 1 ELSE 0 END),
>>> SUM(CASE WHEN pitting_min between 80 and 90 THEN 1 ELSE 0 END),
>>> SUM(CASE WHEN and pitting_min > 90 THEN 1 ELSE 0 END)
>>> count(PITTING_MIN)
>>> from tubedb
>>> where ingot_seq between '4000' and '5000'
>>> and pitting_min > 0
>>> group by ingot_seq
>>> order by ingot_seq
>>>
>>>
>>> John
>>>
>>> .
>>>
>
 >> Stay informed about: Simple Table but having query issues 
Back to top
Login to vote
Erland Sommarskog2

External


Since: May 30, 2004
Posts: 2061



(Msg. 9) Posted: Sat Oct 24, 2009 6:25 am
Post subject: Re: Simple Table but having query issues [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
John Wright (JohnWright@discussions.microsoft.com) writes:
> Thanks one and all, I did find the solution using this syntax below, but I
> hit a big, big snag. The database does not support ANSI 92 and the case
> statement fails. Any idea how to do this without using case?

Fails? Care to post the error message?

Either you are using SQL Server and you did something wrong. Or you are
not using SQL Server, in which case you are simply in the wrong place.


--
Erland Sommarskog, SQL Server MVP, esquel.RemoveThis@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Simple Table but having query issues 
Back to top
Login to vote
John Bell

External


Since: Jun 01, 2004
Posts: 652



(Msg. 10) Posted: Sat Oct 24, 2009 5:25 pm
Post subject: Re: Simple Table but having query issues [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
Other possible solutions, if you can't have derived tables you could use
views:

CREATE VIEW vw AS
SELECT DISTINCT INGOT_SEQ
FROM tubedb
WHERE ingot_seq BETWEEN '4000' AND '5000'
AND pitting_min > 0

CREATE VIEW vw1 AS
SELECT INGOT_SEQ,
COUNT(*) AS CNT1
FROM tubedb
WHERE ingot_seq BETWEEN '4000' and '5000'
AND pitting_min BETWEEN 1 AND 79
GROUP BY ingot_seq

CREATE VIEW vw2 AS
SELECT INGOT_SEQ,
COUNT(*) AS CNT2
FROM tubedb
WHERE ingot_seq BETWEEN '4000' AND '5000'
AND pitting_min BETWEEN 80 AND 90
GROUP BY ingot_seq

CREATE VIEW vw3 AS
SELECT INGOT_SEQ,
COUNT(*) AS CNT3
FROM tubedb
WHERE ingot_seq BETWEEN '4000' AND '5000'
AND pitting_min > 90
GROUP BY ingot_seq

SELECT vw.INGOT_SEQ,
vw1.CNT1,
vw2.CNT2,
vw3.CNT3
FROM vw
LEFT OUTER JOIN vw1 ON vw.INGOT_SEQ = vw1.INGOT_SEQ
LEFT OUTER JOIN vw2 ON vw.INGOT_SEQ = vw2.INGOT_SEQ
LEFT OUTER JOIN vw3 ON vw.INGOT_SEQ = vw3.INGOT_SEQ

If you can't use case what about subqueries?

SELECT vw.INGOT_SEQ,
(SELECT COUNT(*) FROM tubedb vw1 WHERE vw.INGOT_SEQ = vw1.INGOT_SEQ AND
ingot_seq BETWEEN '4000' and '5000' AND pitting_min BETWEEN 1 AND 79 ) AS
CNT1,
(SELECT COUNT(*) FROM tubedb vw1 WHERE vw.INGOT_SEQ = vw1.INGOT_SEQ AND
ingot_seq BETWEEN '4000' and '5000' AND pitting_min BETWEEN 80 AND 90 ) AS
CNT2,
(SELECT COUNT(*) FROM tubedb vw1 WHERE vw.INGOT_SEQ = vw1.INGOT_SEQ AND
ingot_seq BETWEEN '4000' and '5000' AND pitting_min > 90 ) AS CNT3
WHERE ingot_seq BETWEEN '4000' AND '5000'
AND pitting_min > 0


John

"John Bell" wrote in message

> That want' quite right it should have been
>
> SELECT INGOT_SEQ,SUM(SUM1), SUM(SUM2), SUM(SUM3), SUM(SUM4)
> FROM (
> select INGOT_SEQ,
> COUNT(*) AS SUM1,0 AS SUM2,0 AS SUM3 ,0 AS SUM4
> from tubedb
> where ingot_seq between '4000' and '5000'
> and pitting_min > 0
> AND pitting_min between 1 and 79
> group by ingot_seq
> ...
> ) AS A
> order by ingot_seq
>
> Or
>
> SELECT INGOT_SEQ,SUM(SUM1), SUM(SUM2), SUM(SUM3), SUM(SUM4)
> FROM (
> select INGOT_SEQ,
> 1 AS SUM1,0 AS SUM2,0 AS SUM3 ,0 AS SUM4
> from tubedb
> where ingot_seq between '4000' and '5000'
> and pitting_min > 0
> AND pitting_min between 1 and 79
> ...
> ) AS A
> group by ingot_seq
> order by ingot_seq
>
> John
>
> "John Bell" wrote in message
>
>> You could try
>>
>> SELECT INGOT_SEQ,SUM(SUM1), SUM(SUM2), SUM(SUM3), SUM(SUM4)
>> FROM (
>> select INGOT_SEQ,
>> 1 AS SUM1,0 AS SUM2,0 AS SUM3 ,0 AS SUM4
>> from tubedb
>> where ingot_seq between '4000' and '5000'
>> and pitting_min > 0
>> AND pitting_min between 1 and 79
>> group by ingot_seq
>> UNION ALL
>> select INGOT_SEQ,
>> 0,1,0,0
>> from tubedb
>> where ingot_seq between '4000' and '5000'
>> and pitting_min > 0
>> AND pitting_min between 80 and 90
>> group by ingot_seq
>> UNION ALL
>> select INGOT_SEQ,
>> 0,0,1,0
>> from tubedb
>> where ingot_seq between '4000' and '5000'
>> and pitting_min > 0
>> AND pitting_min > 90
>> group by ingot_seq
>> UNION ALL
>> select INGOT_SEQ,
>> 0,0,0,1
>> from tubedb
>> where ingot_seq between '4000' and '5000'
>> and pitting_min > 0
>> AND pitting_min > 90
>> group by ingot_seq
>> ) AS A
>> order by ingot_seq
>>
>> Or use view to do the union
>>
>> John
>>
>> "John Wright" wrote in message
>>
>>> Thanks one and all, I did find the solution using this syntax below, but
>>> I
>>> hit a big, big snag. The database does not support ANSI 92 and the case
>>> statement fails. Any idea how to do this without using case?
>>>
>>> "John Bell" wrote:
>>>
>>>>
>>>> "John Wright" wrote in message
>>>>
>>>> >I have the following table with two columns
>>>> >
>>>> > INGOT_SEQ nvarchar(4)
>>>> > PITTING_MIN smallINT
>>>> >
>>>> >
>>>> > So here is three queries I can run to get three seperate result
>>>> >
>>>> > select INGOT_SEQ, count(PITTING_MIN)
>>>> > from tubedb
>>>> > where ingot_seq between '4000' and '5000'
>>>> > and pitting_min > 0
>>>> > and pitting_min between 1 and 79
>>>> > group by ingot_seq
>>>> > order by ingot_seq
>>>> >
>>>> > select INGOT_SEQ, count(PITTING_MIN)
>>>> > from tubedb
>>>> > where ingot_seq between '4000' and '5000'
>>>> > and pitting_min > 0
>>>> > and pitting_min between 80 and 90
>>>> > group by ingot_seq
>>>> > order by ingot_seq
>>>> >
>>>> > select INGOT_SEQ, count(PITTING_MIN)
>>>> > from tubedb
>>>> > where ingot_seq between '4000' and '5000'
>>>> > and pitting_min > 90
>>>> > group by ingot_seq
>>>> > order by ingot_seq
>>>> >
>>>> >
>>>> > I would like to get these into one row for each INGOT_SEQ and if
>>>> > there is
>>>> > a
>>>> > null value, replace it with a zero. So the output would go from this
>>>> >
>>>> > 4010,5
>>>> > 4010,6
>>>> > 4010,115
>>>> >
>>>> > to this
>>>> >
>>>> > 4010,5,6,115
>>>> >
>>>> > I think this is a subquery or self join, but I can't get the syntax
>>>> > right.
>>>> > Please help.
>>>> >
>>>> Hi
>>>>
>>>> Try something like:
>>>>
>>>> select INGOT_SEQ,
>>>> SUM(CASE WHEN pitting_min between 1 and 79 THEN 1 ELSE 0 END),
>>>> SUM(CASE WHEN pitting_min between 80 and 90 THEN 1 ELSE 0 END),
>>>> SUM(CASE WHEN and pitting_min > 90 THEN 1 ELSE 0 END)
>>>> count(PITTING_MIN)
>>>> from tubedb
>>>> where ingot_seq between '4000' and '5000'
>>>> and pitting_min > 0
>>>> group by ingot_seq
>>>> order by ingot_seq
>>>>
>>>>
>>>> John
>>>>
>>>> .
>>>>
>>
>
 >> Stay informed about: Simple Table but having query issues 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
Query performance issues ! - We are using 64 bit SQL Server 2005 and one of the queries that was executing within 3 minutes fails to return once in a while. The problem is not predictable. The data is increasing every day. It does not seem to be the volume of the data that is causin...

Simple Query that isn't simple - SQL Server 2000 I have a table that records various actions for people. I want to search for actions where none of several other required actions has been done (exception report). So the basic code I am working with (ignoring the people issue). drop...

simple query - Hi, if i had a table with two columns: one an int column (Person_id) and one a navrchar column (Person_First_name) and i wanted to write a query that would return the Person_id and Person_First_name of all the people with the same first name (where th...

Simple query - I am a true newbie in T-SQL. I want to write a store procedure to return a row of a table where the date field is less than today. Can someone help please? HS

Simple Query - SET NOCOUNT ON DECLARE @EndDate DATETIME --SET @EndDate = '2007-01-01 00:00:00.000' --dec 11 DECLARE @tab TABLE(Emp_Id int , planName varchar(10), start_Date datetime, End_date datetime ) INSERT INTO @tab SELECT 1,'p1','20061231','20070101' INSERT...
   Database Help (Home) -> Programming All times are: Pacific Time (US & Canada)
Page 1 of 1

 
 You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]