sql show the records and the total number of records in one result-CodePudding

i have a table like so

USERS

id name    location
1  anna     z
2  billy    x
3  Can      y
4  Diigy    w

i want to write a query that will give me the records and the total number of records i know i can achieve them separately

Select * from users;
1  anna     z
2  billy    x
3  Can      y
4  Diigy    w

select COUNT(id) from users;
4

i am a bit confused on how would i combine this two together.

CodePudding user response：

Ww3 Schools has a good source on this for you here: https://www.w3schools.com/mysql/mysql_join.asp

Create a table or a temp table with the Select you are using to get all the records. Then JOIN the table with the count result and perhaps create a new table with combined table.

(to make the table based on the select statement)

CREATE TABLE tbl_newtable
SELECT * FROM users;

If you made it this far all you need to do is JOIN this table with the COUNT, but the problem is what is the common factor between the count and all the other things in the table? Or maybe you could do an INSERT statement followed by the count statement as another idea.

CodePudding user response：

Use Windowsfunctions to combine your two queries

Select * from users;
1  anna     z
2  billy    x
3  Can      y
4  Diigy    w

select COUNT(id) from users;
4

Use COUNT(id)Over()

Select *,COUNT(id)Over() from users;
1  anna     z  4
2  billy    x  4
3  Can      y  4
4  Diigy    w  4