help with query - 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

Transact-SQL (2000)

help with query

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-05-22 : 12:52:00

I have a query which returns a buch of unique rows with one of the fields being schoolname (where a schoolname could be on 1 - anynumber of records)

Is there anyway for the query to return the amount of rows for each schoolname? (schoolname is a varchar field)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-22 : 13:09:04

[code]
SELECT t.col1, t.col2, t.schoolname, dt.counter
FROM tables t
LEFT OUTER JOIN (
SELECT COUNT(*), schoolname
FROM table
GROUP BY schoolname) dt ON t.schoolname = dt.schoolname
[/code]

Is this what you're looking for?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-05-22 : 13:18:25

not sure ---
her'es my changed query to yorus

SELECT t.orderid from orders t, dt.counter
LEFT OUTER JOIN (
SELECT COUNT(*), afschool
FROM orders
GROUP BY afschool) dt ON t.afschool = dt.afschool

Thanks for your help

I get invalid column name counter -- what should it be there is no counter field.
Also where would i add in to only count it if afschool is not null

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-22 : 13:29:25

SELECT t.orderid from orders t, dt.counter
LEFT OUTER JOIN (
SELECT counter = COUNT(*), afschool
FROM orders
GROUP BY afschool) dt ON t.afschool = dt.afschool

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-05-22 : 13:31:58

but there is no field counter?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-22 : 13:33:44

lol That was stupid on my part.


SELECT o.orderid, dt.counter
FROM orders o1
LEFT OUTER JOIN (
  SELECT COUNT(*) AS counter, afschool
  FROM orders
  GROUP BY afschool) o2 ON o1.afschool = o2.afschool

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-05-22 : 13:50:37

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'o' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dt' does not match with a table name or alias name used in the query.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-22 : 14:03:58

[code]
SELECT o1.orderid, o2.counter
FROM orders o1
LEFT OUTER JOIN (
SELECT COUNT(*) AS counter, afschool
FROM orders
GROUP BY afschool) o2 ON o1.afschool = o2.afschool
[/code]

Ok. This is getting funny now. You know....you probably could have figured this out yourself.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-05-22 : 14:08:24

hi thanks -- but this is not returning waht I need.

Let me explain more -- my orders tables has a list of orders -- one field is afschool.
currently ther is only one record with afscholl but there will be many more. the rest are null.
I want to show all but the null ones.
now the query above is retruning a list of orderid with counter null. orderid is definately different for all the orders.
can you help me with this one?

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-22 : 14:16:29

You really aren't explaining things very well at all here. What you just said is completely different from what you started out with. Are you sure you explained it correctly this time?

SELECT o1.orderid
FROM orders o1
WHERE o1.afschool IS NOT NULL --I want to show all but the null ones.

If you still want the counter field, then change your LEFT OUTER JOIN on the previous post of mine to an INNER JOIN.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-05-22 : 14:27:50

I want to show all where afschool is not null BUT I want to show a list like
school1 20 (if afschool is in the table with 20 entries)
school2 2
school3 5

Am I clear now. I'm confusing myself as well somehow.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-22 : 15:02:18

Did you run the query I gave you earlier with an INNER JOIN instead of a LEFT OUTER JOIN?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-05-22 : 15:10:44

yes but it's not returnign the results I want

it's returnging 156 -1

right now it should return only 1 row school - test counter -1

(but when I add more schools to the records -- it should count it correclty)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-22 : 15:29:50

[code]
CREATE TABLE #orders(orderid INT, afschool VARCHAR(55))

INSERT #orders(orderid, afschool)
SELECT 1,NULL UNION ALL
SELECT 2,NULL UNION ALL
SELECT 3,NULL UNION ALL
SELECT 4,'TEST1' UNION ALL
SELECT 5,'TEST2' UNION ALL
SELECT 6,'TEST3' UNION ALL
SELECT 7,'TEST2' UNION ALL
SELECT 8,'TEST2'

SELECT afschool, COUNT(*) AS counter
FROM #orders
WHERE afschool IS NOT NULL
GROUP BY afschool

DROP TABLE #orders
[/code]

We're both smoking crack today. This isn't that hard. It should have taken you like 2 seconds to get this one, if this is what you are looking for. Is THIS what you are looking for?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2005-05-23 : 01:22:47

thanks!! exactly what i needed --- thanks for your help!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources