How to Count Query Result Rows

How can I count the numbers of rows that a MySQL query returned?

Getting total rows in a query result...

You could just iterate the result and count them. You don't say what language or client library you are using, but the API does provide a mysql_num_rows function which can tell you the number of rows in a result.

This is exposed in PHP, for example, as the mysqli_num_rows function. As you've edited the question to mention you're using PHP, here's a simple example using mysqli functions:

$link = mysqli_connect("localhost", "user", "password", "database");

$result = mysqli_query($link, "SELECT * FROM table1");
$num_rows = mysqli_num_rows($result);

echo "$num_rows Rows\n";

Getting a count of rows matching some criteria...

Just use COUNT(*) - see Counting Rows in the MySQL manual. For example:

SELECT COUNT(*) FROM foo WHERE bar= 'value';

Get total rows when LIMIT is used...

If you'd used a LIMIT clause but want to know how many rows you'd get without it, use SQL_CALC_FOUND_ROWS in your query, followed by SELECT FOUND_ROWS();

SELECT SQL_CALC_FOUND_ROWS * FROM foo
   WHERE bar="value" 
   LIMIT 10;

SELECT FOUND_ROWS();

For very large tables, this isn't going to be particularly efficient, and you're better off running a simpler query to obtain a count and caching it before running your queries to get pages of data.

Count total rows return by query result in SQL Server

Use @@RowCount

select a._PROCESS_INST_NO,a._ISSUER,a._ISSUE_DATE,d._PROCESS_CONTENTS,
            d._OPT_DIVISION,b._GOODS_CD,b._GOODS_NAME,b._QTY,
            a._Order_No,c._GOODS_CD,c._GOODS_NAME,a._NOTE,
            ( ROW_NUMBER() OVER (PARTITION BY a._PROCESS_INST_NO ORDER BY d._PROCESS_CONTENTS) )Proc_Step_No
           ,@@rowCount as Total_Rows
 from [ENVNDIVDB].[dbo].[TBL_PROC_PH] a
inner join [ENVNDIVDB].[dbo].[TBL_PROC_PM] b
on b._PROCESS_INST_NO=a._PROCESS_INST_NO
inner join [ENVNDIVDB].[dbo].[TBL_PROC_PMS] c
on c._PROCESS_INST_NO=a._PROCESS_INST_NO
inner join [ENVNDIVDB].[dbo].[TBL_PROC_PN] d
on d._PROCESS_INST_NO=a._PROCESS_INST_NO
where a._PROCESS_INST_NO='609390'

UPDATE @@Rowcount will not work if you have any query before this query. Try below:

;with testCTE as 
(
select a._PROCESS_INST_NO,a._ISSUER,a._ISSUE_DATE,d._PROCESS_CONTENTS,
            d._OPT_DIVISION,b._GOODS_CD,b._GOODS_NAME,b._QTY,
            a._Order_No,c._GOODS_CD,c._GOODS_NAME,a._NOTE,
            ( ROW_NUMBER() OVER (PARTITION BY a._PROCESS_INST_NO ORDER BY d._PROCESS_CONTENTS) )Proc_Step_No

 from [ENVNDIVDB].[dbo].[TBL_PROC_PH] a
inner join [ENVNDIVDB].[dbo].[TBL_PROC_PM] b
on b._PROCESS_INST_NO=a._PROCESS_INST_NO
inner join [ENVNDIVDB].[dbo].[TBL_PROC_PMS] c
on c._PROCESS_INST_NO=a._PROCESS_INST_NO
inner join [ENVNDIVDB].[dbo].[TBL_PROC_PN] d
on d._PROCESS_INST_NO=a._PROCESS_INST_NO
where a._PROCESS_INST_NO='609390'
)

select * 
, (select count(*) from testCTE) as Total_Rows
from testCTE

You can also use COUNT(*) OVER ()

select a._PROCESS_INST_NO,a._ISSUER,a._ISSUE_DATE,d._PROCESS_CONTENTS,
                d._OPT_DIVISION,b._GOODS_CD,b._GOODS_NAME,b._QTY,
                a._Order_No,c._GOODS_CD,c._GOODS_NAME,a._NOTE,
                ( ROW_NUMBER() OVER (PARTITION BY a._PROCESS_INST_NO ORDER BY d._PROCESS_CONTENTS) )Proc_Step_No
               ,COUNT(*) OVER () as Total_Rows
     from [ENVNDIVDB].[dbo].[TBL_PROC_PH] a
    inner join [ENVNDIVDB].[dbo].[TBL_PROC_PM] b
    on b._PROCESS_INST_NO=a._PROCESS_INST_NO
    inner join [ENVNDIVDB].[dbo].[TBL_PROC_PMS] c
    on c._PROCESS_INST_NO=a._PROCESS_INST_NO
    inner join [ENVNDIVDB].[dbo].[TBL_PROC_PN] d
    on d._PROCESS_INST_NO=a._PROCESS_INST_NO
    where a._PROCESS_INST_NO='609390'

Get count of rows returned by a query result set without ROWCOUNT

Just use your current query as a sub-query.

SELECT COUNT(*)
FROM (
    SELECT E.[EmployeeId], COUNT(*) LoginCount
    FROM tblEmployee E
    JOIN tblAccessLog AL ON E.EmployeeId = AL.EmployeeId
    WHERE AL.[AccessType] = 212
    GROUP BY E.[EmployeeId]
    HAVING COUNT(*) > 100
) X

Select count(*) from result query

You can wrap your query in another SELECT:

select count(*)
from
(
  select count(SID) tot  -- add alias
  from Test 
  where Date = '2012-12-10' 
  group by SID
) src;  -- add alias

See SQL Fiddle with Demo

In order for it to work, the count(SID) need a column alias and you have to provide an alias to the subquery itself.

SQL count rows in a table

Yes, SELECT COUNT(*) FROM TableName

How display result count from query

I'm not sure why you're using the Raw method for this, but I'd like to point out there's a Count method to achieve what you want: http://gorm.io/docs/query.html#Count

db.Where("name = ?", "jinzhu").Or("name = ?", "jinzhu 2").Find(&users).Count(&count)
//// SELECT * from USERS WHERE name = 'jinzhu' OR name = 'jinzhu 2'; (users)
//// SELECT count(*) FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'; (count)

db.Model(&User{}).Where("name = ?", "jinzhu").Count(&count)
//// SELECT count(*) FROM users WHERE name = 'jinzhu'; (count)

I put together a very simple example from the docs:

package main

import (
    "fmt"

    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/sqlite"
)

type Porg struct {
    gorm.Model
    Name string
}

func main() {
    db, err := gorm.Open("sqlite3", "test.db")
    if err != nil {
        panic("failed to connect database")
    }
    defer db.Close()

    // Migrate the schema
    db.AutoMigrate(&Porg{})

    // Create
    for i := 1; i <= 100; i++ {
        db.Create(&Porg{Name: "John"})
    }

    // Read
    var porgs []Porg
    var count int
    db.Model(&porgs).Count(&count)

    fmt.Println(count)
}

Output: 100

Using the Model method you can Specify a model to query, this won't query the DB directly. Using db.Find(&porgs).Count(&count) will actually send 2 SQL queries to the db.

Get number of rows returned in MySQL query

Select count(*)
From
(
    SELECT COUNT(l.product_number_language) as counts, l.id, l.product_number, 
        l.language, l.product_number_language
    FROM bs_products_languages l
    LEFT JOIN bs_products p ON (l.product_number_language = p.product_number)
    WHERE l.product_number = 'C4164' 
    AND l.active='Y'
    AND p.active='Y'
    GROUP BY l.language
) as t

Counting no of rows returned by a select query

SQL Server requires subqueries that you SELECT FROM or JOIN to have an alias.

Add an alias to your subquery (in this case x):

select COUNT(*) from
(
select m.Company_id
from Monitor as m
    inner join Monitor_Request as mr on mr.Company_ID=m.Company_id
    group by m.Company_id
    having COUNT(m.Monitor_id)>=5)  x

---

Related Topics