How to use COUNT in this SQL Query?

Question

My question is really simple, I need to count how many results this query has because there're too many records in this ugly database and the PHP code is using too much memory to pull all the records. I'm gonna count how many records this query generates and with PHP i'm gonna use a loop to pull 10k records at a time.

This is the query:

SELECT p.Email,
            c.ID_Cliente,
            c.DataHoraUltimaAtualizacaoILR,
            p.Nome, 
            upper(substring(p.Nome, 1, charindex(' ', p.Nome + ' '))) as 'PrimeiroNome',
            p.DataHoraCadastro, 
            p.Sexo, 
            p.EstadoCivil, 
            p.DataNascimento, 
            getdate() as [today],
            datediff (yy,p.DataNascimento,getdate()) as 'Idade',
            datepart(month,p.DataNascimento) as 'MesAniversario',
            e.Bairro,
            e.Cidade, 
            e.UF, 
            c.CodLoja as codloja_cadastro,
            t.DDD,
            t.Numero
from PessoaFisica p
left join Cliente c on (c.ID_Pessoa = p.ID_PessoaFisica)
left join Loja l on (cast(l.CodLoja as integer) = cast(c.CodLoja as integer))
left join PessoaEndereco pe on (pe.ID_Pessoa = p.ID_PessoaFisica)
left join Endereco e on (e.ID_Endereco = pe.ID_Endereco)
left join PessoaTelefone pt on (pt.ID_Pessoa = p.ID_PessoaFisica)
left join Telefone t on (t.ID_Telefone = pt.ID_Telefone)
where p.Email is not NULL and p.Email <> ''
group by p.Email, c.ID_Cliente, p.Nome, p.EstadoCivil, p.DataHoraCadastro, c.CodLoja, p.Sexo, e.Bairro, p.DataNascimento, e.Cidade, e.UF, c.DataHoraUltimaAtualizacaoILR, t.DDD, t.Numero

I already tried this (bellow) but the count is different from the total of results of this query, it has to match:

SELECT COUNT(p.Email)
FROM PessoaFisica p
left join Cliente c on (c.ID_Pessoa = p.ID_PessoaFisica)
left join Loja l on (cast(l.CodLoja as integer) = cast(c.CodLoja as integer))
left join PessoaEndereco pe on (pe.ID_Pessoa = p.ID_PessoaFisica)
left join Endereco e on (e.ID_Endereco = pe.ID_Endereco)
left join PessoaTelefone pt on (pt.ID_Pessoa = p.ID_PessoaFisica)
left join Telefone t on (t.ID_Telefone = pt.ID_Telefone)
where p.Email is not NULL and p.Email <> ''

I'm using MS SQL Server

Answer 1

Try to put your query inside SELECT COUNT(*) FROM (...) AS tbl as follows

SELECT COUNT(*) FROM
(
    SELECT 
        p.Email,
        c.ID_Cliente,
        c.DataHoraUltimaAtualizacaoILR,
        p.Nome, 
        upper(substring(p.Nome, 1, charindex(' ', p.Nome + ' '))) as 'PrimeiroNome',
        p.DataHoraCadastro, 
        p.Sexo, 
        p.EstadoCivil, 
        p.DataNascimento, 
        getdate() as [today],
        datediff (yy,p.DataNascimento,getdate()) as 'Idade',
        datepart(month,p.DataNascimento) as 'MesAniversario',
        e.Bairro,
        e.Cidade, 
        e.UF, 
        c.CodLoja as codloja_cadastro,
        t.DDD,
        t.Numero
    from PessoaFisica p
    left join Cliente c on (c.ID_Pessoa = p.ID_PessoaFisica)
    left join Loja l on (cast(l.CodLoja as integer) = cast(c.CodLoja as integer))
    left join PessoaEndereco pe on (pe.ID_Pessoa = p.ID_PessoaFisica)
    left join Endereco e on (e.ID_Endereco = pe.ID_Endereco)
    left join PessoaTelefone pt on (pt.ID_Pessoa = p.ID_PessoaFisica)
    left join Telefone t on (t.ID_Telefone = pt.ID_Telefone)
    where p.Email is not NULL and p.Email <> ''
    group by p.Email, c.ID_Cliente, p.Nome, p.EstadoCivil, p.DataHoraCadastro, 
    c.CodLoja, p.Sexo, e.Bairro, p.DataNascimento, e.Cidade, e.UF, 
    c.DataHoraUltimaAtualizacaoILR, t.DDD, t.Numero
) AS tbl