assistance with SQL (COUNT, GROUP BY, HAVING, etc...)

Question

assistance with SQL (COUNT, GROUP BY, HAVING, etc...)

mmontero

SSC Rookie

Points: 30
More actions
May 4, 2011 at 7:01 pm

#235820

Hi there,
I am trying to create a report to audit parking permits.
3 tables to join.
Now I understand the concept of counts, group by etc.. but I need to count and group by based on a WHERE clause for different permit types and property types?
Possible in 1 SQL statement?
I have the following currently to count for houses and residential permits greater (RP)
select count(prop.Property_key) AS "permits" , prop.Formatted_Address, LC.Minor_description
FROM Infodbo.Licensing_Facts AS LF
inner join Infodbo.Licensing_Locations AS LL ON LF.Licensing_Facts_Key = LL.Licensing_Facts_Key
INNER JOIN Infodbo.Property AS Prop ON LL.Property_Key = Prop.Property_key
INNER JOIN Infodbo.Licensing_Categories AS LC ON LC.Licensing_Facts_Key = LF.Licensing_Facts_Key
where LF.type_code = 'RP'
and LF.expiry_date = '2011-06-30 00:00:00.000'
and LF.Decision_Type_Description = 'APPROVED'
and LC.Minor_description = 'House'
GROUP BY prop.Formatted_Address, LC.Minor_description
HAVING COUNT (prop.Property_key) > 2
order by prop.Formatted_Address asc
Thanks in advance.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004682 More actions · Answer 1

mmontero (5/4/2011)
Possible in 1 SQL statement?

The answer to that question is almost always "Yes". Now, please tell us why you think you need it to be a single query, please.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mmontero SSC Rookie Points: 30 More actions · Answer 2

Thanks, one reason, report will be published in reporting revices where I then add grouping layers to property types.

Basically want it to be on the one page 🙂

Jeff Moden SSC Guru Points: 1004682 More actions · Answer 3

I don't know much about how you pass things (ie: variables, etc) to reports in SSRS but it seems to me that you'd only need to include a variable in the not yet existing WHERE clause. The the folks do it at work is they build a stored procedure and pass IT the criteria to filter on and return a single result set to SSRS.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Jeff Moden (5/5/2011)
I don't know much about how you pass things (ie: variables, etc) to reports in SSRS but it seems to me that you'd only need to include a variable in the not yet existing WHERE clause. The the folks do it at work is they build a stored procedure and pass IT the criteria to filter on and return a single result set to SSRS.

That's what I do here. Not sure I see how you need help at this point.