CODEWITHSUNDEEP
mysqlsql
Haroldas
Haroldas

Reputation: 1010

How to count rows by group by and and add this to subquery?

i need to count rows gouped by Hours and add this to select subquery, but i got error on this line AND DATE(created_at) = T.day_start AND user_id = T.user_id

Here is my query:

SELECT
    COUNT(*)
FROM
    (
        SELECT
            HOUR (call_start_at) AS hours,
            count(*) AS calls
        FROM
            calls
        WHERE
            1
        AND user_id = 8
        AND call_start_at >= '2016-01-06 00:00:00'
        AND call_start_at <= '2016-01-06 23:59:59'
        GROUP BY
            HOUR (call_start_at)
    ) AS T1

And i try this add to select subquery, but wrong on marked line with T.day_start and T.user_id when i changing.

Here is my test:

SELECT
    T2.name,
    T2.calls,
    ROUND(calling_time * 100 / working_time, 2) AS percent,
    T2.calling_time,
    T2.working_time
FROM
    (SELECT
        T.name,
        (SELECT COUNT(*) FROM calls AS C WHERE DATE(C.created_at) = T.day_start AND C.user_id = T.user_id) AS calls,
        (SELECT
            COUNT(*)
        FROM 
        (SELECT
            HOUR(call_start_at) as hours,
            count(*) as calls
        FROM 
            calls
        WHERE 1
            AND DATE(created_at) = T.day_start AND user_id = T.user_id // marked line
        GROUP BY
            HOUR(call_start_at)) as T3
        ) as row_count,
        (SELECT SUM(call_length) FROM calls AS C WHERE DATE(C.created_at) = T.day_start AND C.user_id = T.user_id) AS calling_time,
        SUM(T.working_time) AS working_time
    FROM
        (SELECT
            U.username AS name,
            U.id AS user_id,
            DATE(UW.start) as day_start,
            UW.length AS working_time
        FROM
            users AS U
            LEFT JOIN users_worktime AS UW ON UW.user_id = U.id
        WHERE 1
            AND U.type = 'agent'
            AND UW.start >= '2016-01-06 00:00:00'
            AND UW.start <= '2016-01-06 23:59:59'
        ) AS T
    GROUP BY
        T.name, T.user_id, T.day_start
    ) AS T2

Upvotes: 0

Views: 28

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

You can more simply write the query as:

    SELECT COUNT(DISTINCT HOUR(call_start_at)) as num
    FROM  calls
    WHERE 1 AND
          user_id = 8 AND
          call_start_at >= '2016-01-06 00:00:00' AND
          call_start_at <= '2016-01-06 23:59:59'

This will allow you to use the correlation clause.

Note: This ignores NULL values. I assume that is not a problem (it is easily fixed if it is).

Upvotes: 2

Related Questions