-3

This gives me errors. How do I need to rewrite it?

I want to have the query return both the subject and that row's corresponding count.

SELECT (SELECT subject FROM marks) AS subject, (SELECT count FROM marks) AS count WHERE usr = 'username'

Nemo-Omen
  • 117
  • 9
  • 2
    What do you mean by "that row's corresponding count"? `count` is a function that counts rows; it doesn't work on one row. Or do you have a column named `"count"`? (If so, please rename the column to something other than a MySQL function name.) Can you give us an example (with data) of what you're trying to accomplish? – Ted Hopp Jan 02 '17 at 23:55
  • @TedHopp I will rename, thanks! – Nemo-Omen Jan 02 '17 at 23:59
  • Relevant question: http://stackoverflow.com/questions/2889871/how-do-i-escape-reserved-words-used-as-column-names-mysql-create-table – Ted Hopp Jan 03 '17 at 00:02
  • @TedHopp, `COUNT` is not a reserved keyword. – shmosel Jan 03 '17 at 00:06
  • @shmosel - No, but it's the same fix in the case of a column name being the same as a function name. – Ted Hopp Jan 03 '17 at 00:07
  • @TedHopp There's no problem with naming a column the same as a function name. [Proof](http://sqlfiddle.com/#!9/303612/1/0) – shmosel Jan 03 '17 at 00:11
  • @shmosel - How about that. I was obviously off on the wrong track. – Ted Hopp Jan 03 '17 at 00:12

2 Answers2

2

The subqueries are not required and are breaking your relationship. Try this:

SELECT subject, `count` FROM marks WHERE usr = 'username';

I think count is a column in your table? If so, as @TedHopp points out, since count is also a mysql built-in function, you'll have to escape it to refer to the column name.

http://sqlfiddle.com/#!9/e3cd1/1 is a corresponding fiddle.

erik258
  • 14,701
  • 2
  • 25
  • 31
  • It is a column. If I rename it, how do I select both? – Nemo-Omen Jan 02 '17 at 23:58
  • 1
    @Nemo-Omen - I think Dan just showed you. – Ted Hopp Jan 03 '17 at 00:00
  • 1
    Same way you select both if you _don't_ rename it, just specify the correct name in either case. Or, you can always use the backticks "`" to escape if you don't want to rename. Using reserved keywords as column names isn't "wrong" per se, but it does force you to escape. – erik258 Jan 03 '17 at 00:00
  • `COUNT` is not a reserved keywords. See my comments on the question. – shmosel Jan 03 '17 at 07:40
  • @shmosel, you're arguing semantics, but technically it's a function ( an aggregate function to be exact). The answers address this, so the difference is irrelevant. – erik258 Jan 03 '17 at 10:34
  • Function names don't need to be escaped. How is that semantics? – shmosel Jan 03 '17 at 15:25
  • but the OP is attempting to refer to a *column* not the function with the same name. – erik258 Jan 03 '17 at 15:31
  • Which is not at all a problem. Did you read my comments on the question? – shmosel Jan 03 '17 at 16:22
0
SELECT subject, COUNT(subject) `count`
FROM marks
WHERE usr = 'username'
GROUP BY subject
abigperson
  • 5,252
  • 3
  • 22
  • 25