CODEWITHSUNDEEP
phpmysqlcount
orion
orion

Reputation: 1

Count() from 2 different tables

I have 2 different tables admin_reservations and calendar_events

admin_reservation:

id,

full_name,

reservation_date,

reservation_time

John 03.05.2011 17:00

Mary 03.06.2011 12:00

calendar_events:

id,

username,

reservation_date,

reservation_time

john.boy 02.05.2011 15:00

suzie 03.05.2011 07:00

I want to count like this:

03.05.2011 2

02.06.2011 1

03.06.2011 1

Upvotes: 0

Views: 138

Answers (4)

orion
orion

Reputation: 1

Thanks @Marco for the code but I modified the code a little so it could work:

SELECT dt,COUNT(*) AS tot FROM(
  (
    SELECT reservation_date AS dt
    FROM admin_reservations
  ) 
    UNION ALL    
  (
   SELECT reservation_date AS dt
   FROM calendar_events
  )
)AS foo GROUP BY dt ORDER BY dt

Upvotes: 0

Neil
Neil

Reputation: 55392

It might be faster to sum the counts of each table, but you should compare the execution plans:

SELECT reservation_date, SUM(date_count) FROM
(
    SELECT reservation_date, COUNT(*) AS date_count
    FROM admin_reservations
    GROUP BY reservation_date
    UNION ALL
    SELECT reservation_date, COUNT(*) AS date_count
    FROM calendar_events
    GROUP BY reservation_date
) GROUP BY reservation_date

Upvotes: 0

Marco
Marco

Reputation: 57573

Try this:

SELECT dt,COUNT(id) tot FROM
(
    SELECT id,full_name fn,reservation_date dt,reservation_time
    FROM admin_reservations
    UNION ALL
    SELECT id,username fn,reservation_date dt,reservation_time
    FROM calendar_events
) GROUP BY dt ORDER BY tot desc

Upvotes: 1

Rudie
Rudie

Reputation: 53801

I don't have much time, but I wanna share this:

Do a (select all on table A UNION select all on table B) GOUP BY date.

MySQL UNION

MySQL GROUP BY

Be careful though. This can't be fast =)

Upvotes: 0

Related Questions