mySQL Query Multiple Group By

Question

How would I combine these two queries into 1 and to run efficiently on a large table?

SELECT  field1, count(1) as requestCount
FROM table1
WHERE date_complete >= '2012-06-12 00:00:00' 
      AND date_complete <= '2012-07-12 23:59:59' 
GROUP BY field1

SELECT field2, count(1) as completeCount
FROM table1
WHERE date_complete >= '2012-06-12 00:00:00' 
      AND date_complete <= '2012-07-12 23:59:59' 
GROUP BY field2

Table holds information for a process where multiple users are involved. For example say the first person creates the request, second person completes the request and the third closes out the request by filing it.

I want to count how many each user requested, completed and filed in a specific time frame

I want these two combined to look like

+----------------+--------------+
| field1         | requestCount |
+----------------+--------------+
| PJB            |            1 |
| RFD            |            6 |
| YAS            |            4 |
+----------------+--------------+

+

+---------+---------------+
| field2  | completeCount |
+---------+---------------+
| PJB     |            4  |
| YAS     |            5  |
+---------+---------------+

=

+----------------+--------------+---------------+
| Username       | requestCount | completeCount |
+----------------+--------------+---------------+
| PJB            |            1 |             4 |
| RFD            |            6 |             0 |
| YAS            |            4 |             5 |
+----------------+--------------+---------------+

what is the content of the table and what is your expected result — Sashi Kant
– Sashi Kant, Commented Jul 12, 2012 at 15:29

Robert · Accepted Answer · 2012-07-12 17:06:46Z

1

select  o.USERNAME, a.requestCount , b.completeCount from (SELECT  USERNAME
FROM owner_login_pass )
 as o left join (SELECT field1, count(1) as requestCount
FROM table1
WHERE date_complete between '2012-06-12 00:00:00' and '2012-07-12 23:59:59' 
GROUP BY field1) as a on o.USERNAME=a.field1
left join (SELECT field2, count(1) as completeCount
FROM table1
WHERE date_complete between '2012-06-12 00:00:00' and '2012-07-12 23:59:59' 
GROUP BY field2) as b on o.USERNAME=b.field2

You would need to ensure that field1 has all of the users that you would need to display data for. Otherwise, you would need to join to a users table as well.

edited Jul 12, 2012 at 17:06

Robert

7781 gold badge7 silver badges15 bronze badges

answered Jul 12, 2012 at 16:04

jle

9,5095 gold badges50 silver badges68 bronze badges

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

1 Comment

Robert Over a year ago

this looks like what I want. field1 might not have all the users in field 2. How would I join it from the table to ensure I have all the users. Also "as b on a.field1=b.field1" should be "as b on a.field1=b.field2"

ypercubeᵀᴹ · Accepted Answer · 2012-07-12 16:17:15Z

Just guessing here but I think you need:

SELECT
    u .Username
  , t1.requestCount
  , t2.completeCount
FROM
        ( SELECT field1 AS Username
          FROM table1
          UNION
          SELECT field2
          FROM table2
        ) AS u
    LEFT JOIN
        ( SELECT field1
               , COUNT(*) AS requestCount
          FROM table1
          WHERE date_complete >= '2012-06-12' 
            AND date_complete < '2012-07-13' 
          GROUP BY field1
        ) AS t1  
            ON t1.field1 = u.Username
    LEFT JOIN
        ( SELECT field2
               , COUNT(*) AS completeCount
          FROM table1
          WHERE date_complete >= '2012-06-12' 
            AND date_complete < '2012-07-13' 
          GROUP BY field2 
        ) AS t2
            ON t2.field2 = u.Username   
;

If you have a user table, simply replace the first derived table with user AS u

Niet the Dark Absol · Accepted Answer · 2012-07-12 15:29:42Z

0

I don't think you can combine them (other than with UNION), but you can improve it slightly by using: WHERE date_complete BETWEEN '2012-06-12 00:00:00' AND '2012-07-12 23:59:59'

answered Jul 12, 2012 at 15:29

Niet the Dark Absol

326k86 gold badges480 silver badges604 bronze badges

Comments

Sashi Kant · Accepted Answer · 2012-07-12 16:04:17Z

0

Do you need :::

Select * from 
(
   SELECT  field1, count(1) as requestCount
FROM table1
WHERE date_complete between '2012-06-12 00:00:00' and '2012-07-12 23:59:59'  
GROUP BY field1 ) tab1
left join
(
SELECT fied2, count(1) as completeCount
FROM table1
WHERE date_complete between '2012-06-12 00:00:00' and '2012-07-12 23:59:59' 
GROUP BY field2) tab2 on (tab1.field1 = tab2.field2)

edited Jul 12, 2012 at 16:04

answered Jul 12, 2012 at 15:30

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

5 Comments

Robert Over a year ago

hmm this doesn't output what I was thinking it should.

Sashi Kant Over a year ago

so i asked you for the table content and the result you need

Robert Over a year ago

yeah sorry I updated my question let me know if that does nto help

Sashi Kant Over a year ago

@Robert: please show a snapshot of the result you want in the resultant table

Robert Over a year ago

I updated to what I want. I also have a user's table if that would somehow play into a join to make this possible

Collectives™ on Stack Overflow

mySQL Query Multiple Group By

4 Answers 4

1 Comment

Comments

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related