SQL-query question - 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)

SQL-query question

Author

Topic

schumi
Starting Member

12 Posts

Posted - 2002-01-14 : 05:42:30

I have a table thats called customerInfo. customerInfo has three columns. they are called userId, meterId, and buildingId. i would like to write an SQL-question that gives me every buildingId and count howe many different buildings there is.

i have tryed this question, but i doesn't work. when you run it in an queryanalyzer i looks pretty ok, but when I run it in my asp-code then I get the wrong result.

select buildingId, count(*) as number
from customerInfo
where buildingId in
(select distinct buildingId
from customerInfo
where customerId=111)
group by buildingId

i the table looks like this...

customerId buildingId meterId
111 1 23
111 1 34
111 2 12

then I want my SQL-question to return number=2 (the number of buildings) and buildingId 1 and 2. not 1,1 and 2.

can anyone help me?

Mannga
Yak Posting Veteran

70 Posts

Posted - 2002-01-14 : 06:01:40

If I understand what you are saying try this.

SELECT COUNT(DISTINCT(BuildingID)) from Table group by BuildingID

That hopefully will work, I'm pretty sure someone will let youknow if that is totally wrong

jackstow
Posting Yak Master

160 Posts

Posted - 2002-01-14 : 06:16:17

That will work except for the GROUP BY which seems to give you 1,1 rather than 2. What about splitting it up into two statements?

SELECT DISTINCT buildingId
FROM customerInfo
where customerId=111

SELECT COUNT(DISTINCT(buildingId)) from customerInfo
where customerId=111

Jack

schumi
Starting Member

12 Posts

Posted - 2002-01-14 : 07:00:31

yes i could split the question in two questions, but I prefer not to do that because I think that it will slow down my application.
any more ideas?

Nazim
A custom title

1408 Posts

Posted - 2002-01-14 : 07:48:57

this should do what you are looking for in one query

SELECT buildingid,COUNT(DISTINCT(BuildingID)) as Countt from testt group by BuildingID
order by buildingid
compute sum(COUNT(DISTINCT(BuildingID)))

But Am not too sure about the Ansi complaiance of Compute.

On second thought is this what you want. if the Query you have given works in Query Analyser , y dont u make a stored procedure of it and call it thru Asp.

HTH

----------------------------------
"True love stories don't have endings."

schumi
Starting Member

12 Posts

Posted - 2002-01-14 : 09:26:28

thanks nazim!!!

your SQL-question does what I want.

:)

Nazim
A custom title

1408 Posts

Posted - 2002-01-14 : 10:30:28

My Question or my Answer??

quote:

SELECT buildingid,COUNT(DISTINCT(BuildingID)) as Countt from testt group by BuildingID
order by buildingid
compute sum(COUNT(DISTINCT(BuildingID)))

or

On second thought is this what you want. if the Query you have given works in Query Analyser , y dont u make a stored procedure of it and call it thru Asp.

if its the query i gave, am curious about it. coz i never used compute before.

----------------------------------
"True love stories don't have endings."

schumi
Starting Member

12 Posts

Posted - 2002-01-14 : 10:57:34

hmm...

more problems..

how can I access the sum.
i tryed to access the sum by writing like this, but the compilator does not aprove of it.

SELECT buildingid,COUNT(DISTINCT(BuildingID)) as Count from customerInfo
where customerId=111 group by BuildingID
order by buildingId
compute sum(COUNT(DISTINCT(BuildingID)) as S);

schumi
Starting Member

12 Posts

Posted - 2002-01-14 : 11:04:34

I wrote SQL-question but what i ment to say was SQL-query.
your SQL-query work just as I wanted.
I have also never tryed compute before but it worked just fine.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-01-14 : 17:46:32

Nazim,

I think schumi was getting confused between "Question" and "Query". Hence "your question worked..."

schumi - these words do mean the same thing in English, but Query is used specifically for SQL when talking DBs.

As for your question, I'm confused about what you want. It looks like you're trying to add up the Ids? Perhaps you could re-specify your problem.

Also, I suspect that breaking your code up into two parts will not cause the performance hit you suspect....

I hope that when I die someone will say of me "That guy sure owed me a lot of money"

schumi
Starting Member

12 Posts

Posted - 2002-01-15 : 04:52:02

what I want to do is...

I want to find out how many different buildingId a customer has and at the same time list the buildingId's.
if a customer has tre different buildingId's, then I want the result of the query to say that he has three different buildingId's, even if there are 30 rows in the table that has his buildingId's.

I am trying to do all this from asp-code.(if someone is curious)

pradeepbj
Starting Member

6 Posts

Posted - 2002-01-15 : 07:17:11

Try this..........

Select buildingid, (Select count(distinct buildingid) from testt Where customerid = 111) TotalBuilds
from testt
where customerid = 111
group by buildingid

It will return all the building ids the customer has and also total unique building ids for a customer)

hope this helps!!!

schumi
Starting Member

12 Posts

Posted - 2002-01-18 : 07:41:28

thank you pradeepbj

now it works :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources