How to insert a count column into a sql query

Question

I need the second column of the table retrieved from a query to have a count of the number of rows, so row one would have a 1, row 2 would have a 2 and so on. I am not very proficient with sql so I am sorry if this is a simple task.

A basic example of what I am doing would be is:

SELECT [Name], [I_NEED_ROW_COUNT_HERE],[Age],[Gender]
FROM [customer]

The row count must be the second column and will act as an ID for each row. It must be the second row as the text file it is generating will be sent to the state and they require a specific format.

Thanks for any help.

Answer 1

With your edit, I see that you want a row ID (normally called row number rather than "count") which is best gathered from a unique ID in the database (person_id or some other unique field). If that isn't possible, you can make one for this report with ROW_NUMBER() OVER (ORDER BY EMPLOYEE_ID DESC) AS ID, in your select statement.

select Name, ROW_NUMBER() OVER (ORDER BY Name DESC) AS ID,
Age, Gender
from customer

This function adds a field to the output called ID (see my tips at the bottom to describe aliases). Since this isn't in the database, it needs a method to determine how it will increment. After the over keyword it orders by Name in descending order.

---

Information on Counting follows (won't be unique by row):

If each customer has multiple entries but the selected fields are the same for that user and you are counting that user's records (summed in one result record for the user) then you would write:

select Name, count(*), Age, Gender
from customer
group by name, age, gender

This will count (see MSDN) all the user's records as grouped by the name, age and gender (if they match, it's a single record).

However, if you are counting all records so that your whole report has the grand total on every line, then you want:

select Name, (select count(*) from customer) as "count", Age, Gender
from customer

TIP: If you're using something like SSMS to write a query, dragging in columns will put brackets around the columns. This is only necessary if you have spaces in column names, but a DBA will tend to avoid that like the plague. Also, if you need a column header to be something specific, you can use the as keyword like in my first example.

W3Schools has a good tutorial on count()

The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:

SELECT COUNT(column_name) FROM table_name;

The COUNT(*) function returns the number of records in a table:

SELECT COUNT(*) FROM table_name;

The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:

SELECT COUNT(DISTINCT column_name) FROM table_name; 

COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.

Answer 2

It's odd to repeat the same number in every row but it sounds like this is what you're asking for. And note that this might not work in your flavor of SQL. MS Access?

SELECT [Name], (select count(*) from [customer]), [Age], [Gender]
FROM [customer]