why can't I do this?

[SMAlvarez]

Why is this illegal and how can I fix it? Thanks.

SELECT COUNT(*) AS NumberOfHistory
FROM Enrolls
WHERE COURSENUM = 405;

SELECT COUNT(*) AS NumberOfScience
FROM Enrolls
WHERE COURSENUM = 605;

 

[IanWaterman]

Possibly because you are counting everything ie (*)

Try counting your table key (whatever its name is)
eg

SELECT COUNT(table_key) AS NumberOfHistory
FROM Enrolls
WHERE COURSENUM = 405;

Or are you wanting two columns then

select sum( case when coursenum = 605 then 1 else 0 end ) as NumOfSci,
sum( case when coursenum = 405 then 1 else 0 end ) as NumOfHist
FROM Enrolls

Ian

 

[oharab]

Or are you trying to get 2 rows, in which case use a union

SELECT "History" AS Course, COUNT(*) AS Number
FROM Enrolls
WHERE COURSENUM = 405
UNION
SELECT "Science" AS Cource COUNT(*) AS Number
FROM Enrolls
WHERE COURSENUM = 605;

Or more efficiently:

SELECT COURSENUM, COUNT(*)
FROM Enrolls
WHERE COURSENUM IN (405,605)
GROUP BY COURSENUM

Without knowing a little more about what you want, what error you're getting and what system you're running, it's difficult to provide you with the exact answer you need!

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[r937]

Why is this illegal and how can I fix it?

it isn't, and you don't have to





r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[genomon]

Why is this illegal and how can I fix it?

I agree with r937. If it's "illegal", there must be an error message you can post?

Ever notice how fast Windows runs? Me neither.

 

[alapatis]

plz try this..

SELECT COURSENUM,COUNT(*) AS NumberOfScience
FROM Enrolls
WHERE COURSENUM = 605;