CODEWITHSUNDEEP
sqlsql-server
user2189553
user2189553

Reputation: 1

Count of a column in SQL Server

My table (student) is structured like this:

NAME    ACCOUNT SALARY
a         12     1222
b        113     222222
c         21     2221
d         12     2432

I want to show results like this:

Desired Results

How do I get to select the distinct account count?

Upvotes: 0

Views: 34

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

You would use window functions:

select name, account, salary,
       count(*) over (partition by account) as account_count
from student;

This is not only the simplest way to express the query; it should also have the best performance.

Upvotes: 1

Bernd Buffen
Bernd Buffen

Reputation: 15057

you can use a query like this:

SELECT 
  `NAME`
  ,`ACCOUNT`
  ,`SALARY`
  , (SELECT count(*) FROM abc WHERE `ACCOUNT` = a.`ACCOUNT` ) AS 'ACCOUNT COUNT'
   FROM abc a;

sample

MariaDB []> SELECT
    ->   `NAME`
    ->   ,`ACCOUNT`
    ->   ,`SALARY`
    ->   , (SELECT count(*) FROM abc WHERE `ACCOUNT` = a.`ACCOUNT` ) AS 'ACCOUNT COUNT'
    ->    FROM abc a;
+------+---------+--------+---------------+
| NAME | ACCOUNT | SALARY | ACCOUNT COUNT |
+------+---------+--------+---------------+
| a    | 12      |   1222 |             2 |
| b    | 113     | 222222 |             1 |
| c    | 21      |   2221 |             1 |
| d    | 12      |   2432 |             2 |
+------+---------+--------+---------------+
4 rows in set (0.00 sec)

MariaDB []>

Upvotes: 0

Related Questions