CODEWITHSUNDEEP
sql-serveraggregation
dijikul
dijikul

Reputation: 158

SQL GROUP BY Aggregate

Tickets have an ID, TicketNbr, and Time Entries have an ID, Time_RecID. Tickets have 0, 1, or potentially more time entries associated with the TicketNbr.

I'm trying to generate a list like so:

Time Entries | Tickets
          10 | #
           9 | #
           8 | #
           7 | #
           6 | #
           5 | #
           4 | #
           3 | #
           2 | #
           1 | #

Where # is the number of tickets.

The data comes from two tables: v_rpt_Service (TicketNbr), and v_rpt_Time (Time_RecID). All of my relevant data is discovered using the following SQL:

 SELECT
    s.TicketNbr, t.Time_RecID
FROM
    v_rpt_Service s
    LEFT JOIN v_rpt_time t on s.TicketNbr = t.SR_Service_RecID

Where I'm struggling is the aggregation. Ideally, what I'm looking for is:

SELECT
    COUNT(T.Time_recID) as Time_Entries, COUNT(DISTINCT(s.TicketNbr)) as Tickets
FROM
    v_rpt_Service s
    LEFT JOIN v_rpt_time t on s.TicketNbr = t.SR_Service_RecID
GROUP BY
    COUNT(t.Time_recID)

which results in this error:

Msg 144, Level 15, State 1, Line 8 Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

How do I refactor the initial query to give me output like the list above?

Upvotes: 0

Views: 3996

Answers (2)

Bastiaan
Bastiaan

Reputation: 4682

You can make a nested query:

SELECT TicketNbr, count(Time_RecID)
FROM
(SELECT
    s.TicketNbr, t.Time_RecID
FROM
    v_rpt_Service s
    LEFT JOIN v_rpt_time t on s.TicketNbr = t.Time_RecID) AS table
GROUP BY
Time_RecID

Upvotes: 1

Simon1979
Simon1979

Reputation: 2108

SELECT q.TimeEntries,count(q.TicketNbr) As Tickets
FROM ( SELECT
            s.TicketNbr, count(t.Time_RecID) As TimeEntries
        FROM
            v_rpt_Service s
        LEFT JOIN v_rpt_time t on s.TicketNbr = t.Time_RecID
        GROUP BY s.TicketNbr) As q
GROUP BY q.TimeEntries
ORDER BY q.TimeEntries DESC

You might need to consider what happens when a ticket has no time entries (which I assume may be the case given the LEFT JOIN

Upvotes: 1

Related Questions