CODEWITHSUNDEEP
sqlsql-servert-sql
Venus Vikki
Venus Vikki

Reputation: 23

SQL Server Query group by clause

I have written Query. Below is that query

SELECT
  COUNT(amount) AS NR_Sales,
  DATEPART(YEAR, crn_dt) AS years
FROM c_Payment_service_fee
WHERE CFK_F_key IN (SELECT
  FK_2290F_key
FROM C_Submissions
WHERE FK_C_UP_key IN (SELECT
  PK_C_UP_key
FROM C_User_Profile
WHERE DATEPART(WEEK, crn_dt) BETWEEN 1 AND (DATEPART(WEEK, @date)))
AND DATEPART(WEEK, crn_dt) BETWEEN 1 AND (DATEPART(WEEK, @date)))
AND DATEPART(WEEK, crn_dt) BETWEEN 1 AND (DATEPART(WEEK, @date))
AND amount > 0
GROUP BY DATEPART(YEAR, crn_dt)

And one more 

SELECT
  amount AS NR_Sales,
  DATEPART(YEAR, crn_dt) AS years
FROM c_Payment_service_fee
WHERE CFK_F_key IN (SELECT
  FK_2290F_key
FROM C_Submissions
WHERE FK_C_UP_key IN (SELECT
  PK_C_UP_key
FROM C_User_Profile
WHERE DATEPART(WEEK, crn_dt) BETWEEN 1 AND (DATEPART(WEEK, @date))
AND DATEPART(YEAR, crn_dt) = 2019)
AND DATEPART(YEAR, crn_dt) = 2019
AND DATEPART(WEEK, crn_dt) BETWEEN 1 AND (DATEPART(WEEK, @date)))
AND DATEPART(WEEK, crn_dt) BETWEEN 1 AND (DATEPART(WEEK, @date))
AND amount > 0
AND DATEPART(YEAR, crn_dt) = 2019

Both are working fine no error. But my problem is if i run first query 2019 count will be 303 and if run second query 269 data is displaying of 2019 year.

Where I making mistaking i am not able find the problem please help me with that.

Upvotes: 0

Views: 86

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270843

You first query has:

SELECT COUNT(amount) AS NR_Sales, DATEPART(YEAR, crn_dt) AS years

Your second has:

SELECT amount AS NR_Sales, DATEPART(YEAR, crn_dt) AS years

Your are comparing the count of rows to the sum of a column. I'm not surprised they are different.

I don't know what you want, but I would recommend using this for both queries:

SELECT YEAR(crn_dt), COUNT(*) as num_rows,
       SUM(amount) as total_amount

Upvotes: 0

Mohammad Shehroz
Mohammad Shehroz

Reputation: 236

SELECT COUNT(amount) AS NR_Sales, DATEPART(YEAR, crn_dt) AS years FROM c_Payment_service_fee GROUP BY DATEPART(YEAR, crn_dt)

Upvotes: 0

Virgil Ionescu
Virgil Ionescu

Reputation: 337

SELECT
PK_C_UP_key
FROM C_User_Profile
WHERE DATEPART(WEEK, crn_dt) BETWEEN 1 AND 
(DATEPART(WEEK, @date))
AND DATEPART(YEAR, crn_dt) = 2019

is more restrictive than

SELECT
PK_C_UP_key
FROM C_User_Profile
WHERE DATEPART(WEEK, crn_dt) BETWEEN 1 AND 
(DATEPART(WEEK, @date))

So, the FK_C_UP_key could take more values from subquery in your first query.

Upvotes: 2

Related Questions