Group by Using Parameters in SQL

using parameters in group by statment

You cannot use parameter in the group by clause by you can build query string using parameter. Something like this.

DECLARE @group nvarchar(50) = N'Age_Band'; --set col name
DECLARE @SQLString nvarchar(500);  
SET @SQLString =  
    N'SELECT AVG(Loan_Amount) as Avg_Loan_Amount
 ,count(Loan_Amount)
 ,SUM(Loan_Amount) as Sum_Loan_Amount
 ,SQRT(SUM(Loan_Amount)) as Square_Loan_Amount
 ,' + @group + ' 
 ,SUM(Interest_Paid_Amount) as Sum_Interest_Paid_Amount
 ,GETDATE()as today 
   FROM [dbo].[Loan]   
   group by ' + @group;  
exec  sp_executesql  @SQLString;

T-SQL Dynamic GROUP BY using @parameters

Dynamic SQL is the only way to go here. However, what kind of table do you have where you have a bunch of optional grouping columns?

SQL Server: how to insert a group by parameter using IF

You can use CASE expression here.

GROUP BY (case when x>Y then parameter1 else parameter3 end),parameter2

but it completely depends on what else other column you are used in the select part of your query.

Example Query:-

SELECT  (case when rank1>rank2 then user1 else user2 end) AS Users
        ,location
        ,SUM(Amount) as Value
FROM    Table1
GROUP BY (case when rank1 > rank2 then user1 else user2 end),location

Passing variable in Group By in SQL Server

You need dynamic sql

      EXEC ('select '+@varientsku+',
                    IIf (count(*)>1,''Duplicate'',''True'') as Total 
            from product 
            group by '+@varientsku)

Note : Make sure @varientsku is sanitized, this code is vulnerable to sql injection

SQL procedure group by parameter value

You need to be very careful with this type of code as it is prone to SQL injection.

Always use QUOTENAME on identifiers. And if the column name is coming from user code then verify it.

Because @DUPLICATE_COUNTS is not in scope in the dynamic part, you need to re-declare it as an OUTPUT parameter.

CREATE PROCEDURE getEmpStats (@COLUMN_NAME varchar(20))
AS

DECLARE @DUPLICATE_COUNTS int;

DECLARE @sql nvarchar(max) = N'

        SELECT @DUPLICATE_COUNTS = SUM(DUP_COUNTS)
        FROM 
            (SELECT COUNT(*) AS DUP_COUNTS
             FROM emp
             GROUP BY ' + QUOTENAME(@COLUMN_NAME) + N') AS A;
';

EXEC sp_executesql @sql, N'@DUPLICATE_COUNTS int OUTPUT', @DUPLICATE_COUNTS = @DUPLICATE_COUNTS

GO

To verify the column name, use this code:

IF (NOT EXISTS (SELECT 1
    FROM sys.columns c
    WHERE c.name = @COLUMN_NAME AND c.object_id = OBJECT_ID(N'emp')
    ))
    THROW 50000, 'Non existent column', 0;

Is there a way to use parameters in subquery with groupby and having in sql query?

You can add where clause :

select a.* 
from(select alphabet,date,dense_Rank() over (partition by alphabet order by date) RankOrder
     from mytable mt
     where mt.Alphabet = @palphabet and year(mt.date) = @pyear
     ) a
where RankOrder = 1;

I don't think GROUP BY with HAVING clause is required here. As you have a raw data not the aggregate data & you are returning only 1 row for each alphabet by using where RankOrder = 1.

Note : You can't use INT Alphabet as base table contains text value. So, change the type of variable @palphabet.

Group by Parameters SQL Developer Oracle

The query you use is syntactically OK, but makes not what you probaly intend.

Assume you pass value of 'COLX', the query would be performed as follows

select 'COLX' as P1,  sum(prod), sum(kol), sum(ps)
from promet_3b
group by 'COLX'

This will return one row and ignor the COLX completely.

Here is simple exaple, implementing a logic:

if you pass 1 group on column A
if you pass 2 group on column B

Be carfull that the type of the column are the same

with dt as (
select 'x1' A, 'y' B, 1 cnt from dual union all
select 'x2' A, 'y' B, 1 cnt from dual)
select 
decode(:i,1,A,2,B) grp_col, sum(cnt) from dt
group by decode(:i,1,A,2,B);

passing 1 returns

GRP_COL   SUM(CNT)
------- ----------
x1               1 
x2               1 

passing 2 returns

GRP_COL   SUM(CNT)
------- ----------
y                2 

---

Related Topics