CODEWITHSUNDEEP
phpsqlsql-serversql-server-2008records
Rafael Fragoso
Rafael Fragoso

Reputation: 5

How to use COUNT in this SQL Query?

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

Upvotes: 0

Views: 91

Answers (3)

Deep
Deep

Reputation: 3202

this may also work :

;with cte (Email,ID_Cliente,DataHoraUltimaAtualizacaoILR,Nome, PrimeiroNome,DataHoraCadastro, 
Sexo, EstadoCivil, DataNascimento, today,Idade,MesAniversario,Bairro,Cidade, 
UF, codloja_cadastro,DDD,numero) as 
(
...... your query.....
)
select count(*) from cte

Upvotes: 0

Pred
Pred

Reputation: 9042

After @ekad's answer:

You should not select all the fields and do all the calculations to count the results, you can use only one column or a scalar value too:

SELECT COUNT(*) FROM
(
    SELECT 
        1
    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

Upvotes: 0

ekad
ekad

Reputation: 14614

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

Upvotes: 1

Related Questions