Distinct Count with Group By in SQL Server

elvisidrizi1
 
on Aug 04, 2020 04:53 AM
1178 Views

Hello Everyone,

I have this code that I count a field in my SQL and I have a specific column which I need to count distinct the values but I don't know how to use it in this format

This is the code :

SELECT
        Sum(Price) As Total,
        CONVERT(char(7), date, 120) As [year],
		Department ,
		 Sum Dinstict (CONVERT(INT, CONVERT(VARCHAR(MAX),quantity))) as Lesh
    FROM
        dbo.Expenses
    WHERE
  CONVERT(char(7), date, 120) Between '2020-01' And '" 2020-07"' AND
        Department = 'FixCost' 
    GROUP BY
       CONVERT(char(7), date, 120),
	   Department 
	   )
SELECT
    Total,
     [year],
	 Department as 'Department',
	  Concat(SUM(Convert(int,Convert(Varchar(MAX),(Lesh)))) OVER (),'') As GrandTotal
FROM
    cte
WHERE
     [year] Is Not Null

What I have tried: I need to use it here :

tal,
[year],
Department as 'Department',
Concat(SUM(Convert(int,Convert(Varchar(MAX),(Lesh)))) OVER (),'') As GrandTotal

I tried this, but it doesn't work :

Concat(COUNT(Convert(int,Convert(Varchar(MAX),(Distinct,Lesh)))) OVER (),'')

Thank you, Blessings

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Aug 04, 2020 05:06 AM
on Aug 14, 2020 12:20 AM

Hi elvisidrizi1,

Refer below query.

SQL 

;WITH CTE AS
(
    SELECT
        CAST(SUM(Price) AS VARCHAR(20)) As Total,
        CONVERT(char(7), date, 120) As [Year],
        Department
        ,COUNT(ISNULL(CONVERT(INT, CONVERT(VARCHAR(MAX),quantity)),0)) as Lesh
    FROM dbo.Expenses
    WHERE
    CONVERT(char(7), date, 120) Between '2020-01' And '2020-07' AND Department = 'Grocery'
    GROUP BY CONVERT(char(7), date, 120), Department
)
 
SELECT * FROM CTE
UNION ALL
SELECT 'Total','','',Total FROM 
(
    SELECT TOP 1 COUNT(CONVERT(char(7), date, 120)) Total
    FROM dbo.Expenses
    WHERE CONVERT(char(7), date, 120) Between '2020-01' And '2020-07' AND Department = 'Grocery'
    GROUP BY CONVERT(char(7), date, 120)
    ORDER BY COUNT(CONVERT(char(7), date, 120)) DESC
)t

Output

Total Year Department Lesh
38.04 2020-01 Grocery 1
269.24 2020-03 Grocery 2
611.95 2020-04 Grocery 7
665.81 2020-05 Grocery 11
388.88 2020-06 Grocery 5
412.76 2020-07 Grocery 14
Total     14