Sql-server – Select when group by contains more than one row with the same value

aggregategroup bysql servert-sql

I am trying to write a query that returns one row from table A whenever a column in joined table B contains multiple distinct values for a single matching row in table A. (A -> B is a 1 -> many relationship.) I built a SQL fiddle to demonstrate this in context: http://sqlfiddle.com/#!6/83952/1

In this fiddle, the design column of the perf_ticket_type table should be the same for every ticket_type that has the same perf_id, but I'm trying to select only the instances where it doesn't. So for perf_id 3, there is more than one unique design being returned with the query I'm using currently.

What I want as my result is the two columns of the performance table only for perf_id 3, based on the multiple values of design for that perf_id in the joined table.

I've been frustrated by understanding GROUP BY in the past, so I'm unsure if there's something different I could be doing here to get my desired result. At the moment, I think I can select what I have in the fiddle into a temp table and then do another select on that with a GROUP BY perf_id HAVING COUNT(*) > 1 to get what I want (as per select rows where column contains same data in more than one record), but that seems like it's an extra step.

Best Answer

Your idea is right but you need HAVING COUNT(DISTINCT design) > 1

Like this:

WITH multi_design_perfs AS
(SELECT b.perf_id 
FROM perf_ticket_type b
GROUP BY b.perf_id
HAVING COUNT(DISTINCT b.design) > 1
)
SELECT m.perf_id, 
    STUFF((select ', ' + CAST(b.design AS varchar(10))
     FROM perf_ticket_type b 
     WHERE b.perf_id = m.perf_id
     ORDER BY b.design
     FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,2,'')
FROM multi_design_perfs m
;

Related Solutions

Sql-server – How to select multiple columns but only group by one

In SQL Server you can only select columns that are part of the GROUP BY clause, or aggregate functions on any of the other columns. I've blogged about this in detail here. So you have two options:

Add the additional columns to the GROUP BY clause:

GROUP BY Rls.RoleName, Pro.[FirstName], Pro.[LastName]

Add some aggregate function on the relevant columns:

SELECT Rls.RoleName, MAX(Pro.[FirstName]), MAX(Pro.[LastName])

The second solution is mostly a workaround and an indication that you should fix something more general with your query.

Mysql – Aggregating sum day by day from multiple joins returns unexpected results

I first thought that you needed to GROUP BY the datetable in a derived table as well, to avoid a cross join but it seems that the datetable already has distinct dates so that is not the cause. The problem is the <= in the (more than one) joins. This creates a kind of cross join and wrong results.

So, the solution is to do the LEFT JOIN and the <= inside the derived tables and in the external level join with equality = and no GROUP BY:

Another issue comes from not using GROUP BY DATE(DateColumn). Use that - and then the ON conditions can be simplified as well.

I also changed the DATE(tx.created) <= DATE(dt.thedate) condition to tx.created < (dt.thedate + INTERVAL 1 DAY) so an index on (created, hours) can be used:

SELECT
DATE_FORMAT(dt.thedate, '%Y-%m-%d') AS the_date,
COALESCE(tt.sum_task, 0) AS sum_t,
COALESCE(tg.sum_group, 0) AS sum_g

FROM datetable AS dt

LEFT JOIN   (                                  -- this could be an INNER JOIN, no difference
                SELECT
                dt.thedate,
                SUM(tx.hours) as sum_task

                FROM datetable AS dt
                  LEFT JOIN tasks AS tx
                    ON tx.created < (dt.thedate + INTERVAL 1 DAY)

                 -- Some extra where clauses here   -- move them to the ON above

                GROUP BY dt.thedate
            )
            AS tt ON tt.thedate = dt.thedate

LEFT JOIN   (                                 -- this could be an INNER JOIN, no difference
                SELECT
                dt.thedate,
                SUM(gx.hours) as sum_group

                FROM datetable AS dt
                  LEFT JOIN groups AS gx
                    ON gx.created < (dt.thedate + INTERVAL 1 DAY)

                 -- Some extra where clauses here   -- move them to the ON above

                GROUP BY dt.thedate
            )
            AS tg ON tg.thedate = dt.thedate

ORDER BY dt.thedate ;

Tested at SQLFiddle

Best Answer

Related Solutions

Sql-server – How to select multiple columns but only group by one

Mysql – Aggregating sum day by day from multiple joins returns unexpected results

Related Question