Joe Joe - 6 months ago 12
SQL Question

Aggregating Count in Subquery in SQL

Basically I want Query 1 to give me the results of Query 2. I need to use Query 1 b/c I have other subqueries to add where I am either counting or getting max dates.

I tried Grouping By the

ClientVisit.client_id
in Query 1 but I get this error Column
'ClientVisit.clientvisit_id'
is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
ClientVisit.clientvisit_id
isn’t in the Select list.

Query 1

SELECT ClientVisit.client_id ,
( SELECT COUNT( ZFORM_group_note_28.clientvisit_id )
FROM ZFORM_group_note_28
WHERE ZFORM_group_note_28.successful_day_752410 = 'Yes'
AND ZFORM_group_note_28.clientvisit_id = ClientVisit.clientvisit_id ) AS Successful_Day
FROM ClientVisit
WHERE ClientVisit.visittype_id = 16
AND ClientVisit.program_id = 5
AND ClientVisit.rev_timein >= @param1
AND ClientVisit.rev_timeout < DATEADD( DAY ,
1 ,
@param2 )


Query 2

SELECT ClientVisit.client_id ,
COUNT( ZFORM_group_note_28.clientvisit_id ) AS Successful_Day
FROM ClientVisit
INNER JOIN ZFORM_group_note_28
ON ZFORM_group_note_28.clientvisit_id = ClientVisit.clientvisit_id
WHERE ClientVisit.visittype_id = 16
AND ClientVisit.program_id = 5
AND ZFORM_group_note_28.successful_day_752410 = 'Yes'
AND ClientVisit.rev_timein >= @param1
AND ClientVisit.rev_timeout < DATEADD( DAY ,
1 ,
@param2 )
GROUP BY ClientVisit.client_id


Results:
Query 1

client_id successful_day
1182 1
1182 1
1379 1
1379 1
1379 1
1379 1


Query 2

client_id successful_day
1182 2
1379 9
1523 2
1757 1

Answer

Maybe this will help. (Modified the query by using case statement)

SELECT ClientVisit.client_id ,
Case when ZFORM_group_note_28.successful_day_752410 = 'Yes' then COUNT(ZFORM_group_note_28.clientvisit_id ) else 0 end AS Successful_Day,
Case when ZFORM_group_note_28.successful_day_752410 = 'No' then COUNT(ZFORM_group_note_28.clientvisit_id ) else 0 end AS Unsuccessful_Day
FROM ClientVisit
INNER JOIN ZFORM_group_note_28
ON ZFORM_group_note_28.clientvisit_id = ClientVisit.clientvisit_id
WHERE ClientVisit.visittype_id = 16
  AND ClientVisit.program_id = 5
  AND ClientVisit.rev_timein >= @param1
  AND ClientVisit.rev_timeout < DATEADD( DAY ,  1 , param2 )
GROUP BY ClientVisit.client_id