New answers tagged group-by
1
You should be able to re-use the same variable that lets you pick your columns, like this:
create procedure dynamic_group_by (@params nvarchar(max))
as
declare @sql nvarchar(max)
set @sql = 'select ' + @params + ' from profiles p
where email like ''%@%''
group by ' + @params
print sql
exec sp_executesql @sql
1
You want to do a LATERAL join, by putting the the function in the FROM list. If you just put the function in the from list after table "a", you don't need the LATERAL keyword, as joins to functions are automatically lateral. If you wrap it in a dummy "select * from", then you do need the keyword:
WITH A AS (SELECT DISTINCT trip_id FROM stop_times)
SELECT ...
1
If the gap between matches is always more than 3 days, you could use this:
;WITH CTE AS
(
SELECT DateField,
CASE WHEN DATEDIFF(day,LAG(DateField) OVER(ORDER BY DateField),DateField)> 3 THEN 1 ELSE 0 END as newround,
Hometeam,
AwayTeam
FROM dbo.Matches
)
SELECT Datefield, HomeTeam,AwayTeam, SUM(newround) OVER(ORDER BY DateField) +...
0
SELECT country,
COUNT(state) total,
SUM(state='xxx') xxx,
SUM(state='yyy') yyy,
SUM(state='zzz') zzz
FROM table
GROUP BY country
Top 50 recent answers are included
