CODEWITHSUNDEEP
mysqljoinsubquery
mybigman
mybigman

Reputation: 339

Combine MySQL queries

I am struggling to find away to combine these 2 queries... ive tried subquerys, joins, unions and have had no luck :(

Query A

SELECT DATE(crtdtime) AS cntldate, sd_class, COUNT(crtdtime) AS created
FROM master
WHERE DATE(crtdtime) = '2011-11-16'
GROUP BY cntldate, sd_class

Which produces

+------------+----------+---------+
| cntldate   | sd_class | created |
+------------+----------+---------+
| 2011-11-16 | CUST     |    2226 |
| 2011-11-16 | NET      |     238 |
+------------+----------+---------+

Query B

SELECT DATE(rstdtime) AS cntldate, sd_class, COUNT(rstdtime) AS restored
FROM master
WHERE DATE(rstdtime) = '2011-11-16'
GROUP BY cntldate, sd_class

Which produces

+------------+----------+----------+
| cntldate   | sd_class | restored |
+------------+----------+----------+
| 2011-11-16 | CUST     |     2315 |
| 2011-11-16 | NET      |      221 |
+------------+----------+----------+

But would like the end result to be...

+------------+----------+---------+----------+
| cntldate   | sd_class | created | restored |
+------------+----------+---------+----------+
| 2011-11-16 | CUST     |    2226 |     2315 |
| 2011-11-16 | NET      |     238 |      221 |
+------------+----------+---------+----------+

Would greatly appreciate any help.

Thank you.

Upvotes: 3

Views: 274

Answers (5)

Icarus
Icarus

Reputation: 63956

I believe this will work:

SELECT DATE(m1.crtdtime) AS cntldate, 
m1.sd_class, 
COUNT(m1.crtdtime) AS created , 
(
  SELECT COUNT(m2.rstdtime) 
  FROM master m2
  WHERE DATE(m2.rstdtime) = DATE(m1.rstdtime)
  and m2.sd_class=m1.sd_class
) as restored
FROM master m1
WHERE DATE(crtdtime) = '2011-11-16'
GROUP BY cntldate, sd_class

Upvotes: 0

DRapp
DRapp

Reputation: 48139

It actually isn't that difficult (once you've been doing it a while). Use a SUM( IF() ) such as...

SELECT 
      date( if( date( ctrdtime ) = '2011-11-16', ctrdtime, rstdtime )) as CntlDate,
      sd_class,
      sum( if( date( ctrdtime ) = '2011-11-16', 1, 0 ) ) as CreatedCount,
      sum( if( date( rstdtime ) = '2011-11-16', 1, 0 ) ) as RestoredCount
   FROM 
      master
   WHERE 
         DATE(crtdtime) = '2011-11-16'
      OR DATE(rstdtime) = '2011-11-16'
   GROUP BY 
      1, 2

This would also work if you expanded a date range for both created and restored...

Upvotes: 0

Brian Hoover
Brian Hoover

Reputation: 7991

one possible way

SELECT DATE(crtdtime) AS cntldate, 
  sd_class, 
  sum( DATE(crtdtime) = '2011-11-16') AS created
  sum( DATE(rstdtime) = '2011-11-16') AS restored
FROM master
WHERE DATE(crtdtime) = '2011-11-16' or DATE(rstdtime) = '2011-11-16'
GROUP BY cntldate, sd_class

Upvotes: 2

dMb
dMb

Reputation: 9337

How about 

select q1.cntldate, q1.sd_class, q1.created, q2.restored 
   from  (
   SELECT DATE(crtdtime) AS cntldate, sd_class, COUNT(crtdtime) AS created
      FROM master
      WHERE DATE(crtdtime) = '2011-11-16'
      GROUP BY cntldate, sd_class
   )  q1
   inner join (
      SELECT DATE(rstdtime) AS cntldate, sd_class, COUNT(rstdtime) AS restored
         FROM master
         WHERE DATE(rstdtime) = '2011-11-16'
         GROUP BY cntldate, sd_class
  ) q2
      on q1.cntldate=q2.cntldate and q1.sdclass = q2.sdclass

This is Postgres syntax though, so might have to play with it to get it running with MySql

Upvotes: 0

Yahia
Yahia

Reputation: 70369

use

SELECT C.cntldate, C.sd_class, C.created, R.restored
FROM
(
SELECT DATE(crtdtime) AS cntldate, sd_class, COUNT(crtdtime) AS created
FROM master
GROUP BY cntldate, sd_class
) C
INNER JOIN
(
SELECT DATE(rstdtime) AS cntldate, sd_class, COUNT(rstdtime) AS restored
FROM master
GROUP BY cntldate, sd_class
) R
ON R.cntldate = C.cntldate AND R.sd_class = C.sd_class
WHERE C.cntldate = '2011-11-16' AND R.cntldate = '2011-11-16'

Upvotes: 0

Related Questions