how do I write single query for both count and list.

Hi Experts,

select count(person_Id) from person ;
select person_Id,firstname,lastname from person ;
is it possible to get the above two queries into a single query.
thanks,
LVL 2
srikoteshAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pawingAnalystCommented:
0
srikoteshAuthor Commented:
I am getting the below error when I use union all.
the used select statement have a different no of columns
0
srikoteshAuthor Commented:
I am getting the below error when I use union all.
the used select statement have a different no of columns
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

pawingAnalystCommented:
when using union the columns should be the same, to get around with it use cast and alias all fields that are not in select count() query.  

the pseudocode will be like.


select  
        count(person_id) as person_count,
        cast(1 as int) as person_id,
        cast('' as varchar(20)) as firstname,  <-- varchar should be the same length are you firstname field
        cast('' as varchar(20)) as lastname
from
        person
group by
         person_id,
         firstname,
         lastname

UNION ALL
 select
    cast(0 as int) as person_count,
    person_id,
    firstname,
    lastname,
from
   person
0
pawingAnalystCommented:
hmmm.. remove the person_id from the "group by"
0
srikoteshAuthor Commented:
Tried the below query getting error
select  
        count(person_id) as person_count,
        cast(1 as int) as person_id,
        cast('' as varchar(45)) as first_name,  
        cast('' as varchar(45)) as last_name
from
        person
group by
         first_name,
         last_name

UNION ALL
 select
    cast(0 as int) as person_count,
    person_id,
    first_name,
    last_name,
from
   person
   
       
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'int)
as person_id,
        cast('' as varchar(45)) as first_name,
        cast' at line 3
0
pawingAnalystCommented:
try replacing int with UNSIGNED

cast (0 as UNSIGNED) as person_id,
0
pawingAnalystCommented:
and cast(0 as UNSIGNED) as person_count
0
Julian HansenCommented:
You are probably looking for SQL_CALC_FOUND_ROWS

This article discusses how to use it in a pagination context
http://www.experts-exchange.com/articles/12238/Pagination-with-PHP-and-Mysql.html

You can also read more about it here
http://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_found-rows
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
srikoteshAuthor Commented:
error
use right syntax near unsigned as person_id
0
pawingAnalystCommented:
hmm try enclosing 1 with ''  cast('1' as UNSIGNED) as person_id and cast('0' as UNSIGNED) as person_count.
0
srikoteshAuthor Commented:
now this time at first name
use right syntax near varchar(45)as first_name
0
srikoteshAuthor Commented:
hi hansen,
thanks for your post. I will look into it.
0
pawingAnalystCommented:
can you post your sql statement.

is there a space in between '' ?    cast('' as varchar(45)) as first_name to cast(' ' as varchar(45))
0
srikoteshAuthor Commented:
select  
        count(person_id) as person_count,
        cast('1' as UNSIGNED) as person_id,
        cast('' as varchar(45)) as first_name,  
        cast('' as varchar(45)) as last_name
from
        person
group by
         first_name,
         last_name

UNION ALL
 select
    cast('0' as UNSIGNED) as person_count,
    person_id,
    first_name,
    last_name,
from
   person
0
pawingAnalystCommented:
have you tried putting a space in between the single quote? cast('' as varchar(45)) as first_name to
cast(' ' as varchar(45)) as first_name. do the same also with the last_name.
0
srikoteshAuthor Commented:
yes
0
srikoteshAuthor Commented:
tried with space as well still same issue
0
pawingAnalystCommented:
select  
         count(person_id) as person_count,
         cast('1' as UNSIGNED) as person_id,
         cast(0 as varchar(45)) as first_name,  
         cast(0 as varchar(45)) as last_name
 from
         person
 group by
          first_name,
          last_name

 UNION ALL
  select
     cast('0' as UNSIGNED) as person_count,
     person_id,
     first_name,
     last_name,
 from
    person


try this and see what will be the outcome. I just replace the ' ' with 0 and cast to varchar.
0
srikoteshAuthor Commented:
same issue
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.