How do I use COUNT DISTINCT in SQL code ?

Question

0.00/5 (No votes)

See more:

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 :

Total,
     [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

Posted 3-Aug-20 10:30am

Member 13410460

Updated 3-Aug-20 23:48pm

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2020-08-03T23:48:00

This is not an answer, but the instruction how to make your query working.

1. Instead of this:

SQL

CONVERT(char(7), date, 120) As [year]

use:

SQL

YEAR([date]) As [year]

You don't need to convert date to string to get year. There's in-built function, YEAR (Transact-SQL) - SQL Server | Microsoft Docs[^]
BTW: date is ms sql server data type, so i'd suggest to put the field name between [] brackets.

2. Instead of this:

SQL

SUM(CONVERT(INT, CONVERT(VARCHAR(MAX),quantity)))

use:

SUM(quantity)

Double conversion from integer to varchar and back is redunant!

3. Instead of this:

SQL

WHERE CONVERT(char(7), date, 120) Between '2020-01' And '" 2020-07"'

use:

SQL

WHERE [date] Between '2020-01-01' And '2020-07-31'

If the [date] field is used to group data, you need to "convert it" into single date by using DATEFROMPARTS[^] or DATEADD[^] function.

SQL

DATEFROMPARTS(YEAR([date]), MONTH([date]), 1) --returns 1. day of month
--or
DATEADD(DD, - DAY([date]) +1, [date]) --returns 1. day of month

[EDIT]

If you would like to get distinct values as a comma separated list, check this out: Converting row values in a table to a single concatenated string - SQLMatters[^]

Good luck!

How do I use COUNT DISTINCT in SQL code ?

1 solution

Solution 1

Add your solution here

Preview 0

How do I use COUNT DISTINCT in SQL code ?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us