SQL Condition by user id

Transact-SQL
1

Hi, I am looking for script query on how to group the table below:

Untitled

I have tried this but this is not what I want:

SELECT 
      CAST([Date] AS DATE) AS [TransactionDate]
      SUM([Amount]) AS [TotalCashAmount]
FROM [dbo].[Payments]
WHERE [Name] = 'Cash' AND [UserId] = 7
GROUP BY CAST([Date] AS DATE) 
ORDER BY [TransactionDate];
2

I'm not sure why you want 38.5 on UserID 7 while it has also UserID 5. Do you want the MAX UserID or the last UserID? Or do you want no UserID at all?

SELECT 
      CAST([Date] AS DATE) AS [TransactionDate],
      SUM([Amount]) AS [TotalCashAmount],
      MAX([UserID] AS UserID
FROM [dbo].[Payments]
WHERE [Name] = 'Cash' 
GROUP BY CAST([Date] AS DATE) 
ORDER BY [TransactionDate];
1 Like
3

I want to sum amount of daily cash only if user 7 show on that day.

4 

SELECT 
      CAST([Date] AS DATE) AS [TransactionDate]
      SUM([Amount]) AS [TotalCashAmount]
FROM [dbo].[Payments]
WHERE [Name] = 'Cash'
GROUP BY CAST([Date] AS DATE) 
HAVING MAX(CASE WHEN [UserId] = 7 THEN 1 ELSE 0 END) = 1 --<<--
ORDER BY [TransactionDate];
1 Like