How to select multiple fields with mysql

Question

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'

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
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
@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

Daniel Farrell · Answer 1 · 2017-01-02T23:59:43.030

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.

edited Jan 02 '17 at 23:59

answered Jan 02 '17 at 23:54

Daniel Farrell

11,706
2
25
27

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. – Daniel Farrell 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. – Daniel Farrell 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. – Daniel Farrell 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

score 0 · Answer 2 · answered Jan 02 '17 at 23:57

0

SELECT subject, COUNT(subject) `count`
FROM marks
WHERE usr = 'username'
GROUP BY subject

answered Jan 02 '17 at 23:57

abigperson

4,983
3
21
25

How to select multiple fields with mysql

2 Answers2