Menu
I have one stored procedure...which has one parameter: @subCustName.
This stored proc has one select statement with group by custName, subCustName
Ex:
SELECT custName, subCustName,sum(membership)
FROM CUSTOMER_TABLE
GROUP BY
custName, subCustName
If parameter @subCustName is 'ALL', I dont want to group by subCustName.
How do I acheve that ?
Thanks in advance!
886
You can use a SELECT command with a GROUP BY clause to group all rows that have identical values in a specified column or combination of columns, into a single row. You can also find the aggregate value for each group of column values.
The SQL GROUP BY Statement The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
No, you can't LIMIT subqueries arbitrarily (you can do it to a limited extent in newer MySQLs, but not for 5 results per group). This is a groupwise-maximum type query, which is not trivial to do in SQL.
Many programmers continue to overlook helpful SQL Server features that have been available for years. Most of these overlooked features can simplify your queries, optimize their performance, and improve your productivity. One such feature is T-SQL's GROUP BY ALL option.
Group on a CASE statement?
SELECT
CASE WHEN @custName = 'all' THEN '' ELSE custName END AS custName,
subCustName, sum(membership)
FROM
CUSTOMER_TABLE
GROUP BY
CASE WHEN @custName = 'all' THEN '' ELSE custName END, subCustName
Another example in my answer here: SQL Server 2005/2008 Group By statement with parameters without using dynamic SQL?
185
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
