CODEWITHSUNDEEP
sql-serverdatabasereporting-services
Bean Huang
Bean Huang

Reputation: 159

How to make a summarized group by using SQL?

I created a SQL stored procedure:

CREATE PROCEDURE [stored procedure name]
    (@Location [varchar](255),
     @start_date datetime,
     @end_date datetime)
AS
BEGIN
    SELECT id, location, name, begin_date, age, career, phone_number
    FROM information
    WHERE [begin_date] BETWEEN @start_date AND @end_date 
    AND [location] = @Location
END

And there are different groups in location: Shanghai, New York, Toronto, Sydney, Tokyo. But now I would like to have one more category: All. This category will display all locations. So I can use it in SSRS.

What I wrote:

select 1 as sort_order, 'All' as location
union
select distinct 2 as sort_order, location from information
order by sort_order, location

But this will only return a dropdown list of:

"All", "Shanghai", "New York", "Toronto", "Sydney", "Tokyo"

When I click on Shanghai, New York, Toronto, Sydney, Tokyo, it works fine and display corresponding data. But when I click on "All", there is no data showing up. How can I alter my code to achieve the goal?

Upvotes: 0

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

I think you need to take All into account:

and ([location] = @Location or @location = 'All')

Often, a NULL value is used, to avoid conflict with valid values:

and ([location] = @Location or @location is null)

Upvotes: 1

Related Questions