CODEWITHSUNDEEP
sql-server
Darren
Darren

Reputation: 89

SQL to select all records or a specific record

Given a table

period | location | value
     1          1    100
     1          1     50
     1          2    100
     1          3    100
     2          1    100
     2          2    100
     2          3    100

is it possible to write a single sql command that would group and return the period information by both all locations and a single location?

eg

for all locations:

select period, sum(value) from table group by period

returns

1, 350
2, 300

for a single location:

SELECT period
 ,sum(value)
FROM TABLE
WHERE location = 1
GROUP BY period

returns

1, 150
2, 200

I would like to end up with a single sql command, and if I pass 0 as the location it would return the sum totals for all locations and if I pass a specific location then it would return just that location.

I don't want to use an in() command or have the command changed at runtime since it adds processing time. The actual SQL is quite complicated and using the in() would hit performance heavily.

I have a tried a few logical operations but can't get it to work.

I have also though of using a union but still not sure what the commands would be to filter themselves, plus that's really two seperate SQL commands which adds processing overhead.

I have thought of having one part of the where clause as a not equal to, but there that doesn't allow for the multiple locations.

Upvotes: 1

Views: 91

Answers (3)

Ygautomo
Ygautomo

Reputation: 874

You could create a simple Store Procedure for this problem. Create a SP with parameter pLocation, and call that SP with appropriate select value. This is a sample with MySQL code.

BEGIN
/* Parameter : IN pLocation VARCHAR(1) */

SET @ssql = CONCAT("SELECT period, SUM(value)");
SET @sqlwhere01 = CONCAT("WHERE 1 = 1 ");
SET @sqlgroupby = CONCAT("GROUP BY period)

IF pLocation = 0 THEN
    SET @sqlwhere02 = "";
ELSE
    SET @sqlwhere02 = CONCAT("AND location = ",pLocation);
ENDIF

SET @ssqlexecute = CONCAT(@ssql,@sqlwhere01,@sqlwhere02,@sqlgroupby);

PREPARE statement FROM @ssqlexecute;
EXECUTE statement;
DEALLOCATE PREPARE statement;

END

To run the Store Procedure put this command in your code.

CALL sp_name(plocation);

Upvotes: 0

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

Try using CASE condition in WHERE clause

select period, sum(value) 
from   table 
where  location = CASE WHEN @location = 0 THEN location ELSE @location END
group by period

Upvotes: 2

Squirrel
Squirrel

Reputation: 24763

Is this what you want ?

select period, sum(value) 
from   table 
where  location = @location
or     @location = 0
group by period

Upvotes: 2

Related Questions