CODEWITHSUNDEEP
sql-servert-sql
bumble_bee_tuna
bumble_bee_tuna

Reputation: 3563

Group by with aggregate or a subquery

How can I do this (not production, know it's not pretty):

SELECT mat1_01_06, count(mat1_01_06), MAX(mat_no),
MAX(
        (
          SELECT 
          document.mat_no
          FROM TimeMatters11.lntmu11.document 
          WHERE matter.sysid = document.mat_id and ccode = 'SUMS' 
        )
    )
FROM TimeMatters11.lntmu11.matter
WHERE con_no in 
('PR12-221' , '...', '...)
AND mat1_01_06 != ''
GROUP BY mat1_01_06
HAVING count(mat1_01_06) > 1
ORDER BY count(mat1_01_06) desc

The output is

Msg 130, Level 15, State 1, Line 7
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

I would just like the mat_no that has a child document coded 'SUMS'

Edit 2

Output without subquery

Index                Cnt    mat_no(not useful)
112565/11            25     12-61692
16601/11             12     12-58850
34934/11             12     12-58854
34935/11             12     12-61983
704612/12            12     12-55487
712166/12            11     12-55613
707588/12            9      12-55604
91394/11             8      12-57115

Desired

Index           Cnt     Mat_no that contains doc 'SUMS' 
112565/11   25      12-61692
16601/11    12      12-58850
34934/11    12      12-58854
34935/11    12      12-61983
704612/12   12      12-55487
712166/12   11      12-55613
707588/12   10      12-55604

The mat_no is a many to one relationship to the index, the specific mat_no that has a child doc 'SUMS' is the master record. The one I need to then focus on. Sorry lil confusing.

Upvotes: 0

Views: 610

Answers (1)

EricZ
EricZ

Reputation: 6205

Is this what you want?

SELECT mat1_01_06, count(mat1_01_06), MAX(mat_no),
       Mat_no =  (
          SELECT 
          MAX(document.mat_no)
          FROM TimeMatters11.lntmu11.document 
          WHERE matter.sysid = document.mat_id and ccode = 'SUMS' 
        )
FROM TimeMatters11.lntmu11.matter
WHERE con_no in 
('PR12-221' , '...', '...')
AND mat1_01_06 != ''
GROUP BY mat1_01_06
HAVING count(mat1_01_06) > 1
ORDER BY count(mat1_01_06) desc

Upvotes: 2

Related Questions