0

How do I get the column "count(division)" instead of getting the actual number of counts?

select * from num_taught;

gets me this

enter image description here

select count(division) from num_taught;

gets me this, but I actually want the third column "count(division)" from the previous image

enter image description here

I want to know this because I'm doing this right now:

    sql> select * from num_taught as a, num_taught as b
...> where a.count(division) = b.count(division);
Error: near "(": syntax error

but as you can see, there's a syntax error and I think it's because the code is not referencing the "count(division)" columns but actually finding the count instead.

My end goal is to output the "Titles" that have the same "Division" and have the same count(division).

So for example, the end table would have the rows "Chief Accountant", "Programmer Trainee", "Scrivener", "Technician", "Wizard". Since these are the rows that have a match in division and count(division)

Thanks!

user3085496
  • 175
  • 1
  • 2
  • 10
  • What [tag:rdbms] are you using? – Mureinik Sep 11 '20 at 16:57
  • sorry, im pretty new to coding so im not really sure what you mean. It's from this public website through my school if you want to try it out. num_taught is sql> create table num_taught as ...> select title, division, count(division) from records group by title; The tables should all be preloaded in already. Thanks! – user3085496 Sep 11 '20 at 16:59
  • 1
    Do you have idea what database product is behind that website? Different database products have (slightly) different syntax, and this question will probably boil down to escape that column name, which may look a bit different depending on the database. – Mureinik Sep 11 '20 at 17:01
  • lol ngl idk, it's prob sql lite if that helps at all. If not, that's chill too – user3085496 Sep 11 '20 at 17:03

3 Answers3

2

What does DESC num_taught return? I am curious how the third column is populated - is it some kind of pseudo-column? You may want try wrapping the column name with [], see: How to deal with SQL column names that look like SQL keywords?

i.e. try:

select [count(division)] from num_taught;
sleep furiously
  • 128
  • 8
1

You need to escape your column name using quotes (in case it's Sqlite like you mentioned in the comments).

select "count(division)" from num_taught;

or:

select * from num_taught as a, num_taught as b
 where a."count(division)" = b."count(division)";

If you don't you are using the count-function provided by your Database-system. It's very unusual to name a column like this, it might be either a trap by your tutor or an error while initializing the table in your case.

Christoph Sonntag
  • 4,459
  • 1
  • 24
  • 49
0

I think you just want a count(distinct):

select count(distinct division)
from num_taught;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • When I do this, it gives me column name = count(distinct division) and one row with the number 3. If possible, I want the third column of num_taught so when I join two num_taughts together later, I can compare the third column. I'll put the goal of what i'm trying to do in the post. – user3085496 Sep 11 '20 at 17:05