How to get sum of distinct from group concatinate using comma separator

Question

1.00/5 (1 vote)

See more:

This is my sample table, and my expected output from totalchar:

+--------+--------------+-----------+
+---No---+-----letter---+-totalcha--+
+--------+--------------+-----------+
+---111--+--a,a,b,b,c,c-+-----3-----+  -> count distinct values only
+---222--+--e,e,f,g,h,i-+-----5-----+  -> count distinct values only
+--------+--------------+-----------+

Im using this query:

SQL

GROUP_CONCAT(distinct(letter) SEPARATOR ', ') as letter, sum(CHAR_LENGTH(letter) - CHAR_LENGTH(REPLACE((letter), ',', '')) + 1) as totalchar

And my output is these, which is wrong, can any body can help? thanks in advance:

+--------+--------------+-----------+
+---No---+-----letter---+-totalcha--+
+--------+--------------+-----------+
+---111--+--a,a,b,b,c,c-+-----6-----+  -> count distinct values only
+---222--+--e,e,f,g,h,i-+-----6-----+  -> count distinct values only
+--------+--------------+-----------+

Posted 23-Nov-14 16:36pm

Member 11088078

Updated 23-Nov-14 16:42pm

PIEBALDconsult

v2

Add a Solution

Comments

Shweta N Mishra 24-Nov-14 3:15am

What are the result you expect against each row, Add that also i your question to make it more clear.

Jörgen Andersson 24-Nov-14 5:56am

Do you have any specific reasons to break the First Normal Form?
http://en.wikipedia.org/wiki/First_normal_form

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

DamithSL · Answer 1 · 2014-11-23T17:07:00

Solution 1

try with CHAR_LENGTH(REPLACE(GROUP_CONCAT(distinct(letter) SEPARATOR ', '), ',', ''))

Posted 23-Nov-14 17:07pm

DamithSL

Updated 23-Nov-14 18:56pm

v2

Comments

Member 11088078 24-Nov-14 0:14am

Invalid use of group function

Member 11088078 24-Nov-14 1:51am

could you complete the answer? because it still Invalid use of group function, I think a have some error with my code Im using this combined with your code.....sum(CHAR_LENGTH(letter) - CHAR_LENGTH(REPLACE(GROUP_CONCAT(distinct(letter) SEPARATOR ', '), ',', '')) + 1)

DamithSL 24-Nov-14 2:16am

check this http://sqlfiddle.com/#!2/df4b1a/1[^]

Shweta N Mishra 24-Nov-14 3:15am

this wouldnt work for 222

lokesh lokesh · Answer 2 · 2014-11-23T19:29:00

Hi
Dude

Try this Solution

SQL

Declare @Temp as table (Sno int,Letter nvarchar(50),TotalChar int)
insert INTO @Temp VALUES(1,'a,a,b,b,c,c',0)
insert INTO @Temp VALUES(2,'e,e,f,g,h,i',0)

SELECT sno,Letter,
(SELECT count(DISTINCT Data) from dbo.Split((Letter),','))as Count
from @Temp

Before Execution (Split Function)

Create this function in your DB

CREATE FUNCTION [dbo].[Split]
(
@Line nvarchar(MAX),
@SplitOn nvarchar(5) = ','
)
RETURNS @RtnValue table
(
Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Data nvarchar(100) NOT NULL
)
AS
BEGIN
IF @Line IS NULL RETURN

DECLARE @split_on_len INT = LEN(@SplitOn)
DECLARE @start_at INT = 1
DECLARE @end_at INT
DECLARE @data_len INT

WHILE 1=1
BEGIN
SET @end_at = CHARINDEX(@SplitOn,@Line,@start_at)
SET @data_len = CASE @end_at WHEN 0 THEN LEN(@Line) ELSE @end_at-@start_at END
INSERT INTO @RtnValue (data) VALUES( SUBSTRING(@Line,@start_at,@data_len) );
IF @end_at = 0 BREAK;
SET @start_at = @end_at + @split_on_len
END

RETURN
END