displaying count values as two different columns in mysql

Question

I have data in a MySQL table in the following format. I want to retrieve the count in two different conditions as shown in the query below, I want to combine these queries into a single one, by which I mean I would like the first query result in one column and second query result in another column, as so:

Expected output:

   count      totalcount
   --------------------------
   3          6

Queries:

 select count(*) as count from entries where 
 date between '2014-08-12' and '2014-08-14';

 select count(*) as totalcount from entries ;

Data in mysql table:

  id          date
  ------------------------
  1           2014-08-14
  2           2014-08-13
  3           2014-08-12
  4           2014-08-11
  5           2014-08-10
  6           2014-08-09

sql fiddle http://sqlfiddle.com/#!2/faeb26/6

fancyPants · Accepted Answer · 2014-08-14 13:43:09Z

1

select sum(date between '2014-08-12' and '2014-08-14'), count(*) as totalcount from entries ;

The boolean expression in SUM() equals to true or false, 1 or 0. Therefore just use SUM() instead of COUNT().

answered Aug 14, 2014 at 13:43

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

LHristov · Accepted Answer · 2014-08-14 13:45:00Z

0

Just put the two queries together:

select count(*) as count, b.totalcount from entries, 
  (select count(*) as totalcount from entries) b 
where date between '2014-08-12' and '2014-08-14';

answered Aug 14, 2014 at 13:45

LHristov

1,1217 silver badges16 bronze badges

Comments

Conffusion · Accepted Answer · 2014-08-14 13:45:02Z

0

select sum(c) as count, sum(tc) as totalcount
 from (select count(*) as c , 0 as tc from entries where date between '2014-08-12' and '2014-08-14'
        union all
       select 0 as c, count(*) as tc from entries)

answered Aug 14, 2014 at 13:45

Conffusion

4,5152 gold badges19 silver badges30 bronze badges

Comments

Girish · Accepted Answer · 2014-08-14 13:48:56Z

0

simple combine to result in on other select query try this

SELECT (select count(*) as count from entries where 
 date between '2014-08-12' and '2014-08-14'
) as count, (select count(*) as totalcount from entries) as totalcount;

DEMO LINK

answered Aug 14, 2014 at 13:48

Girish

12.2k3 gold badges37 silver badges54 bronze badges

Collectives™ on Stack Overflow

displaying count values as two different columns in mysql

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related