CODEWITHSUNDEEP
sql
Babasaheb Matsagar
Babasaheb Matsagar

Reputation: 146

Clause in aggregate function

I want to retrieve data in a specific period from TableTransaction (ID_Cust, CustName, TransValue, TransDate, ...)

In SQL below I want to add clause like: 

Where TransDate>1/1/2016 And TransDate<2/1/2016

Code:

SELECT 
    ID_Cust, CustName, SUM(TransValue) As TotalPurchase
FROM
    TTransaction
GROUP BY
    ID_Cust, CustName
HAVING
    SUM(TransValue) < 25000000
ORDER BY
    TotalPurchase DESC

I tried these attempts, but none of them worked.

SELECT 
    ID_Cust, CustName, SUM(TransValue) As TotalPurchase
FROM 
    TTransaction
WHERE
    TransDate > 1/1/2016 AND TransDate < 2/1/2016
GROUP BY
    ID_Cust, CustName
HAVING
    SUM(TransValue) < 25000000
ORDER BY
    TotalPurchase DESC

SELECT 
    ID_Cust, CustName, SUM(TransValue) As TotalPurchase
FROM 
    TTransaction
WHERE
    TransDate > 1/1/2016 AND TransDate < 2/1/2016
HAVING
    SUM(TransValue) < 25000000
ORDER BY
    TotalPurchase DESC

Upvotes: 3

Views: 41

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

First, you need single quotes around date constants. Second, use ISO standard date formats. So, try this:

Select ID_Cust, CustName, SUM(TransValue) As TotalPurchase
From TTransaction
Where TransDate > '2016-01-01' AND TransDate < '2016-02-01'
Group By ID_Cust, CustName
Having SUM(TransValue)<25000000
Order By TotalPurchase DESC

Upvotes: 1

Related Questions