CODEWITHSUNDEEP
mysqlsql
khuss
khuss

Reputation: 325

Add column with count(*)

When I do "select count(*) from users", it returns the data in the following format:

mysql> select count(*) from users;
+----------+
| count(*) |
+----------+
|        100 |
+----------+
1 row in set (0.02 sec)

I would like to get the data in the following format instead.

+---------+----------+
| key     | count    |
+---------+----------+
| my_count|   100    |
+---------+----------+

The reason is to feed this data to a pre-built widget which expects the data in the above format.

Is there a way to do this in SQL?

I tried various options such as "group by" but couldn't get it working.

mysql> select count(*) from users;

+---------+----------+
| key     | count    |
+---------+----------+
| my_count|   100    |
+---------+----------+

Upvotes: 0

Views: 59

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

Just add a string literal to your select clause:

SELECT 'my_count' AS `key`, COUNT(*) AS count
FROM users;

Note that key is a reserved keyword in MySQL, so we must escape it using backticks.

If you intended to use GROUP BY, then you probably want a query like this:

SELECT `key`, COUNT(*) AS count
FROM users
GROUP BY `key`;

Upvotes: 6

Related Questions