urgent please.. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

urgent please..

Author

Topic

aylin_sk
Starting Member

25 Posts

Posted - 2004-12-31 : 03:33:02

How can i get output like this from this sql??

Output:
First row: initial values of the fields
Second row: average of the same fields

Please help me...

select * from (
select '2' OrderNumber, ROWID R, HEM_PATIENT_ID, HEM_LASTDOCTOR_ID, HEM_LASTPHARMA_ID, HEM_LASTDOCTOR_REP_ID, HEM_LASTDOCTOR_REP_NAME, HEM_LASTDOCTOR_REGION, HEM_LASTPHARMA_REP_ID,HEM_LASTPHARMA_REGION, HEM_HEMOGRAMDATE,
HEM_LOKOSIT, HEM_NNS, HEM_EOS, HEM_BAS, HEM_LIM, HEM_MON, HEM_DOZE, HEM_PACK1, HEM_PACK2
from LPMS.HEMOGRAMS
where HEM_PATIENT_ID = 77
union
select '1' OrderNumber, ROWID R, HEM_PATIENT_ID, HEM_LASTDOCTOR_ID, null HEM_LASTPHARMA_ID, null HEM_LASTDOCTOR_REP_ID, null HEM_LASTDOCTOR_REP_NAME, null HEM_LASTDOCTOR_REGION, null HEM_LASTPHARMA_REP_ID, null HEM_LASTPHARMA_REGION, HEM_HEMOGRAMDATE,
AVG(HEM_LOKOSIT), AVG(HEM_NNS), AVG(HEM_EOS), AVG(HEM_BAS), AVG(HEM_LIM), AVG(HEM_MON), AVG(HEM_DOZE), AVG(HEM_PACK1), AVG(HEM_PACK2)
from LPMS.HEMOGRAMS
where HEM_PATIENT_ID = 77
group by ROWID, HEM_PATIENT_ID, HEM_LASTDOCTOR_ID, HEM_HEMOGRAMDATE)
order by OrderNumber desc nulls last;

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-12-31 : 04:38:46

won't this work..


SELECT TOP 1 '2' OrderNumber, ROWID R, HEM_PATIENT_ID, HEM_LASTDOCTOR_ID, HEM_LASTPHARMA_ID, HEM_LASTDOCTOR_REP_ID, HEM_LASTDOCTOR_REP_NAME, HEM_LASTDOCTOR_REGION, HEM_LASTPHARMA_REP_ID,HEM_LASTPHARMA_REGION, HEM_HEMOGRAMDATE,HEM_LOKOSIT, HEM_NNS, HEM_EOS, HEM_BAS, HEM_LIM, HEM_MON, HEM_DOZE, HEM_PACK1, HEM_PACK2
  FROM LPMS.HEMOGRAMS 
 WHERE HEM_PATIENT_ID = 77
UNION ALL
SELECT '1' OrderNumber, ROWID R, HEM_PATIENT_ID, HEM_LASTDOCTOR_ID, null HEM_LASTPHARMA_ID, null HEM_LASTDOCTOR_REP_ID, null HEM_LASTDOCTOR_REP_NAME, null HEM_LASTDOCTOR_REGION, null HEM_LASTPHARMA_REP_ID, null HEM_LASTPHARMA_REGION, HEM_HEMOGRAMDATE,AVG(HEM_LOKOSIT), AVG(HEM_NNS), AVG(HEM_EOS), AVG(HEM_BAS), AVG(HEM_LIM), AVG(HEM_MON), AVG(HEM_DOZE), AVG(HEM_PACK1), AVG(HEM_PACK2)
  FROM LPMS.HEMOGRAMS
 WHERE HEM_PATIENT_ID = 77
 GROUP BY OrderNumber, R, HEM_PATIENT_ID, HEM_LASTDOCTOR_ID, HEM_LASTPHARMA_ID, HEM_LASTDOCTOR_REP_ID, HEM_LASTDOCTOR_REP_NAME, HEM_LASTDOCTOR_REGION, HEM_LASTPHARMA_REP_ID, HEM_LASTPHARMA_REGION, HEM_HEMOGRAMDATE

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

aylin_sk
Starting Member

25 Posts

Posted - 2004-12-31 : 04:48:04

Near SELECT TOP 1, it gave an error telling FROM not found where expected..
And why did u use TOP 1??

aylin_sk
Starting Member

25 Posts

Posted - 2004-12-31 : 05:09:25

Please help me...

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-12-31 : 05:15:49

quote:
Originally posted by aylin_sk

And why did u use TOP 1??

.. because you said, you only wanted the FIRST row with initial values and SECOND row with averages.

Did you want all the initial values first and then average values? If so, you can remove TOP 1

A similar syntax (below) on syscolumns worked for me. Not sure why you are getting the error. Can you post your DDL.

select top 1 '2' OrderNumber, id, xusertype from syscolumns where id = 2
union all
select '1' OrderNumber, id, avg(xusertype) from syscolumns where id = 2 group by id

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

aylin_sk
Starting Member

25 Posts

Posted - 2004-12-31 : 05:22:31

I want all the initial values first and then average values.

This code is working but not calculating the average..

select * from (
select '2' OrderNumber, ROWID R, HEM_PATIENT_ID, HEM_LASTDOCTOR_ID, HEM_LASTPHARMA_ID,
HEM_LASTDOCTOR_REP_ID, HEM_LASTDOCTOR_REP_NAME,
HEM_LASTDOCTOR_REGION, HEM_LASTPHARMA_REP_ID,
HEM_LASTPHARMA_REGION, HEM_HEMOGRAMDATE,
HEM_LOKOSIT, HEM_NNS, HEM_EOS, HEM_BAS,
HEM_LIM, HEM_MON, HEM_DOZE, HEM_PACK1, HEM_PACK2
from LPMS.HEMOGRAMS
where HEM_PATIENT_ID = 77
union
select '1' OrderNumber, ROWID R, HEM_PATIENT_ID, HEM_LASTDOCTOR_ID,
null HEM_LASTPHARMA_ID, null HEM_LASTDOCTOR_REP_ID,
null HEM_LASTDOCTOR_REP_NAME, null HEM_LASTDOCTOR_REGION,
null HEM_LASTPHARMA_REP_ID, null HEM_LASTPHARMA_REGION, HEM_HEMOGRAMDATE,
AVG(HEM_LOKOSIT), AVG(HEM_NNS), AVG(HEM_EOS), AVG(HEM_BAS), AVG(HEM_LIM),
AVG(HEM_MON), AVG(HEM_DOZE), AVG(HEM_PACK1), AVG(HEM_PACK2)
from LPMS.HEMOGRAMS
where HEM_PATIENT_ID = 77
group by ROWID, HEM_PATIENT_ID, HEM_LASTDOCTOR_ID, HEM_HEMOGRAMDATE)
order by OrderNumber desc nulls last;

This code is working so good but i dont know how to do it like this according to my code:
select * from (
select '2' OrderNumber, HEM_PATIENT_ID, HEM_LOKOSIT, HEM_NNS
from LPMS.HEMOGRAMS
where HEM_PATIENT_ID = 79
union
select '1' OrderNumber, HEM_PATIENT_ID, AVG(HEM_LOKOSIT), AVG(HEM_NNS)
from LPMS.HEMOGRAMS
where HEM_PATIENT_ID = 79
group by HEM_PATIENT_ID)
order by OrderNumber desc;

aylin_sk
Starting Member

25 Posts

Posted - 2005-01-03 : 06:11:35

Please help me to calculate the average of the fields...The code works but it doesnt calculate the averages only...

select * from (

select '2' OrderNumber, ROWID R, HEM_PATIENT_ID, HEM_LASTDOCTOR_ID, HEM_LASTPHARMA_ID,

HEM_LASTDOCTOR_REP_ID, HEM_LASTDOCTOR_REP_NAME,

HEM_LASTDOCTOR_REGION, HEM_LASTPHARMA_REP_ID,

HEM_LASTPHARMA_REGION, HEM_HEMOGRAMDATE,

HEM_LOKOSIT, HEM_NNS, HEM_EOS, HEM_BAS,

HEM_LIM, HEM_MON, HEM_DOZE, HEM_PACK1, HEM_PACK2

from LPMS.HEMOGRAMS

where HEM_PATIENT_ID = 77

union

select '1' OrderNumber, ROWID R, HEM_PATIENT_ID, HEM_LASTDOCTOR_ID,

null HEM_LASTPHARMA_ID, null HEM_LASTDOCTOR_REP_ID,

null HEM_LASTDOCTOR_REP_NAME, null HEM_LASTDOCTOR_REGION,

null HEM_LASTPHARMA_REP_ID, null HEM_LASTPHARMA_REGION, HEM_HEMOGRAMDATE,

AVG(HEM_LOKOSIT), AVG(HEM_NNS), AVG(HEM_EOS), AVG(HEM_BAS), AVG(HEM_LIM),

AVG(HEM_MON), AVG(HEM_DOZE), AVG(HEM_PACK1), AVG(HEM_PACK2)

from LPMS.HEMOGRAMS

where HEM_PATIENT_ID = 77

group by ROWID, HEM_PATIENT_ID, HEM_LASTDOCTOR_ID, HEM_HEMOGRAMDATE)

order by OrderNumber desc nulls last;

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-03 : 07:08:41

Looks very strangely that you include ROWID into GROUP BY part.

My suggestion is (I removed ROWID from GROUP BY; and I can bet

the HEM_LASTDOCTOR_ID, HEM_HEMOGRAMDATE should be removed

from there as well):

union

select '1' OrderNumber, NULL, HEM_PATIENT_ID, HEM_LASTDOCTOR_ID,

null, null, null, null, null, null, HEM_HEMOGRAMDATE,

AVG(HEM_LOKOSIT), AVG(HEM_NNS), AVG(HEM_EOS), AVG(HEM_BAS), AVG(HEM_LIM),

AVG(HEM_MON), AVG(HEM_DOZE), AVG(HEM_PACK1), AVG(HEM_PACK2)

from LPMS.HEMOGRAMS

where HEM_PATIENT_ID = 77

group by HEM_PATIENT_ID, HEM_LASTDOCTOR_ID, HEM_HEMOGRAMDATE)

aylin_sk
Starting Member

25 Posts

Posted - 2005-01-03 : 08:31:26

I did like this and it still doesnt calculate the average..It brings all th records only...And when i delete HEM_LASTDOCTOR_ID, HEM_HEMOGRAMDATE, "group by" wants those...

select * from (
select '2' OrderNumber, NULL, HEM_PATIENT_ID, HEM_LASTDOCTOR_ID, HEM_LASTPHARMA_ID,
HEM_LASTDOCTOR_REP_ID, HEM_LASTDOCTOR_REP_NAME, HEM_LASTDOCTOR_REGION, HEM_LASTPHARMA_REP_ID,
HEM_LASTPHARMA_REGION, HEM_HEMOGRAMDATE, HEM_LOKOSIT, HEM_NNS, HEM_EOS, HEM_BAS, HEM_LIM, HEM_MON,
HEM_DOZE, HEM_PACK1, HEM_PACK2
from LPMS.HEMOGRAMS
where HEM_PATIENT_ID = 77
union
select '1' OrderNumber, NULL, HEM_PATIENT_ID, HEM_LASTDOCTOR_ID,
null, null, null, null, null, null, HEM_HEMOGRAMDATE,
AVG(HEM_LOKOSIT), AVG(HEM_NNS), AVG(HEM_EOS), AVG(HEM_BAS), AVG(HEM_LIM),
AVG(HEM_MON), AVG(HEM_DOZE), AVG(HEM_PACK1), AVG(HEM_PACK2)
from LPMS.HEMOGRAMS
where HEM_PATIENT_ID = 77
group by HEM_PATIENT_ID, HEM_LASTDOCTOR_ID, HEM_HEMOGRAMDATE)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-03 : 08:40:12

Try this

select * from (
select '2' OrderNumber, NULL, HEM_PATIENT_ID, HEM_LASTDOCTOR_ID, HEM_LASTPHARMA_ID,
HEM_LASTDOCTOR_REP_ID, HEM_LASTDOCTOR_REP_NAME, HEM_LASTDOCTOR_REGION, HEM_LASTPHARMA_REP_ID,
HEM_LASTPHARMA_REGION, HEM_HEMOGRAMDATE, HEM_LOKOSIT, HEM_NNS, HEM_EOS, HEM_BAS, HEM_LIM, HEM_MON,
HEM_DOZE, HEM_PACK1, HEM_PACK2
from LPMS.HEMOGRAMS
where HEM_PATIENT_ID = 77
union
select '1' OrderNumber, NULL, HEM_PATIENT_ID, NULL,
null, null, null, null, null, null, NULL,
AVG(HEM_LOKOSIT), AVG(HEM_NNS), AVG(HEM_EOS), AVG(HEM_BAS), AVG(HEM_LIM),
AVG(HEM_MON), AVG(HEM_DOZE), AVG(HEM_PACK1), AVG(HEM_PACK2)
from LPMS.HEMOGRAMS
where HEM_PATIENT_ID = 77
group by HEM_PATIENT_ID)

aylin_sk
Starting Member

25 Posts

Posted - 2005-01-03 : 08:41:27

Heyyyyy!You did it!!!!! :))So many thanksss...you are the only one who solved my problem!!

aylin_sk
Starting Member

25 Posts

Posted - 2005-01-03 : 08:44:09

I have a question, why did we delete rowid??I have to use it..my project manager had told me like that..

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-03 : 08:48:12

quote:
Originally posted by aylin_sk

you are the only one who solved my problem!!

That's because I like those awesome medecine terms.

aylin_sk
Starting Member

25 Posts

Posted - 2005-01-03 : 08:50:14

Is it a joke?:) well if i should use rowid, then what should i do?

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-03 : 08:53:51

quote:
Originally posted by aylin_sk

my project manager had told me like that..

Be I you I'd tell him: "#@%%&&& #$@%%# @#&$%^!"

aylin_sk
Starting Member

25 Posts

Posted - 2005-01-03 : 08:54:55

lol :)) Really why cant i use rowid??I'm serious..

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-03 : 09:02:59

Ohhh!!!

Sorry man!!!

But WHY on earth you removed ROWID from the 1st part of

the whole select (before "union")??? Keep this part intact!

It works (and worked) OK!

aylin_sk
Starting Member

25 Posts

Posted - 2005-01-03 : 09:05:29

Oh ok it workedd :))By the way im a girl :)not a man..Thanks so much again...byesss..

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-03 : 09:18:02

lol.. you never know with those girls..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources