CODEWITHSUNDEEP
sqldb2subquery
dukedevil294
dukedevil294

Reputation: 1305

SQL Subquery Select with Same Group By

I don't think this is possible but I'm hopeful there's a way to do it of which I'm not aware. I have this query currently:

SELECT month(app.created) AS Month, year(app.created) AS Year, count(*) AS Total, 
    (SELECT month(app.created) AS Month, year(app.created) AS Year, count(*) AS Total
    FROM lwmod10ht/oaapappl AS app
    INNER JOIN lwmod10ht/oaapposi AS pos
    ON pos.username = app.username
    WHERE pos.jobclass IS NOT NULL AND pos.requisition IS NULL
    GROUP BY month(app.created), year(app.created) 
    ORDER BY year(app.created) asc, month(app.created) asc) AS Direct_Count
FROM lwmod10ht/oaapappl AS app
GROUP BY month(app.created), year(app.created), Direct_Count
ORDER BY year(app.created) asc, month(app.created) asc

It's purpose is to get the number of applications and group them by month/year over a given time period. Then, the Direct_Count column is attempting to break that overall count down into a sub-category.

So the ideal result would be the month, year, overall count, direct count. Now I know that you can't return more than one column in a subquery but since my group by columns will be the same, I'm thinking there has to be a way for it to be able to match up those values? Or will I just need to run two completely separate queries?

Upvotes: 0

Views: 1178

Answers (2)

The Impaler
The Impaler

Reputation: 48770

Adding a couple of cleaner solutions to the same question:

1. Using FILTER (warning: it does NOT work)

If DB2 implemented the new standard SQL:2003 FILTER clause, you could write something like:

SELECT 
    month(app.created) AS Month,
    year(app.created) AS Year,
    count(*) AS Total,
    count(*) FILTER (
      WHERE pos.jobclass IS NOT NULL AND pos.requisition IS NULL
      ) AS direct_count
  FROM lwmod10ht/oaapappl AS app
  GROUP BY month(app.created), year(app.created)
  ORDER BY year(app.created) asc, month(app.created) asc;

But it's just wishful thinking.

2. Poor Man's FILTER (works well and it's faster)

This solution runs a single pass of the data, so it's faster than the accepted answer. This is solution is usually known as "poor man's FILTER":

SELECT 
    month(app.created) AS Month,
    year(app.created) AS Year,
    count(*) AS Total,
    sum(CASE
      WHEN pos.jobclass IS NOT NULL AND pos.requisition IS NULL THEN 1
      ELSE 0
      END) AS direct_count
  FROM lwmod10ht/oaapappl AS app
  GROUP BY month(app.created), year(app.created)
  ORDER BY year(app.created) asc, month(app.created) asc;

Upvotes: 1

The Impaler
The Impaler

Reputation: 48770

Since the filtering condition of both queries is different (they select different rows) you need two queries.

However, I would put them separate in the from clause (instead of using a scalar subquery) and would join them there to give me flexibility on what columns to select and so for. For example:

select x.*, y.direct_count
  from (
    SELECT 
        month(app.created) AS Month,
        year(app.created) AS Year,
        count(*) AS Total
      FROM lwmod10ht/oaapappl AS app
      GROUP BY month(app.created), year(app.created)
      ORDER BY year(app.created) asc, month(app.created) asc
    ) x left join (
    SELECT 
        month(app.created) AS Month, 
        year(app.created) AS Year, 
        count(*) AS direct_count
      FROM lwmod10ht/oaapappl AS app
      JOIN lwmod10ht/oaapposi AS pos ON pos.username = app.username
      WHERE pos.jobclass IS NOT NULL AND pos.requisition IS NULL
      GROUP BY month(app.created), year(app.created) 
      ORDER BY year(app.created) asc, month(app.created) asc
    ) y on x.year = y.year and x.month = y.month;

Upvotes: 1

Related Questions