How to make aggregate count based on function name ?

Question

1.00/5 (1 vote)

See more:

How to make aggregate count based on function name ?
I need to resume my query below or add code to my code below
I need to aggregate by function Name
on second column CountDoneCode is make count to 1 based on function name
on third column CountNotDoneCode is make count to 0 based on function name

Final result I need is

FunctionName	CountDoneCode	CountNotDoneCode
------------    -------------   ----------------
Lifecycle	           3	    0
Rohs	               0	    3
Reach	               3	    0
FMD	                   1	    2
Parametric	           1	    2
Package                3	    0
IntroductionDate       2	    1
MFG	                   2	    1
Qualification	       2	    1

What I have tried:

SQL

create table #Donecode (
  FunctionId   int,
  FunctionName nvarchar(50)
)
insert into #Donecode
values
(1,'Lifecycle'),
(2,'Rohs'),
(3,'Reach'),
(4,'FMD'),
(5,'Parametric'),
(6,'Package'),
(7,'IntroductionDate'),
(8,'MFG'),
(9,'Qualification')

create table #filedetails (
  FileID   int,
  DoneCode nvarchar(50)
)
insert into #filedetails (FileID,DoneCode) 
values 
(3301,'101011111'),
(3301,'101101111'),
(3301,'101001000')

select  substring (Donecode,1,1) as Lifecycle,
        substring (Donecode,2,1) as Rohs,
        substring (Donecode,3,1) as Reach,
        substring (Donecode,4,1) as FMD,
        substring (Donecode,5,1) as Parametric,
        substring (Donecode,6,1) as Package,
        substring (Donecode,7,1) as IntroductionDate,
        substring (Donecode,8,1) as MFG,
        substring (Donecode,9,1) as Qualification
into #FunctionsDiv
from #filedetails
where  DoneCode is not NULL 
and    fileid=3301

declare @SeletColumnComma varchar(max)

select @SeletColumnComma = coalesce(@SeletColumnComma + ',','')
                         + coalesce('substring(DoneCode ,' + cast (FunctionId as nvarchar(20)) + ',1) as ' + FunctionName + '','')
from   #Donecode

select @SeletColumnComma

DECLARE @query nvarchar(max)
SET     @query='select ' + @SeletColumnComma  + '  from #filedetails where DoneCode is not null '
exec    (@query)

drop table #filedetails
drop table #Donecode
drop table #FunctionsDiv

Posted 31-Mar-20 13:59pm

ahmed_sa

Updated 1-Apr-20 10:37am

MadMyche

v3

Add a Solution

Comments

MadMyche 31-Mar-20 20:43pm

It only took a few minutes to format your post, and now that it looks somewhat reasonable perhaps some people will try to help you.
If you can't put in the time to post your issue nicely; what makes you think people will spend any time trying to help you?

Patrice T 31-Mar-20 22:10pm

ahmed is only 260+ questions on this. I think he will never learn.

MadMyche 31-Mar-20 23:35pm

Well aware of that... And I think I have found 2 or 3 different ways to rewrite this already

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)

OriginalGriff · Accepted Answer · 2020-03-31T20:03:00

Solution 1

You have been here for ten years, you have asked 267 questions, and in all that time, all that effort put in by volunteers, you have shown no attempt whatsoever to learn anything from what we have told you.

This is a trivial task, and resembles several of those I recall you asking before.
So tell me, why should we waste our time any further keeping a help vampire in a job he is not only unqualified for, but which he doesn't want to be educated to fit into?

Persuade me that you are learning, that you are trying to actually improve, and I might help you out. But for the moment I for one am done with you.

Posted 31-Mar-20 20:03pm

OriginalGriff

Comments

Patrice T 1-Apr-20 2:14am

Agreed, my 5

Maciej Los 1-Apr-20 2:27am

5 to power of number of question asked by OP!

ahmed_sa 1-Apr-20 3:41am

thank you very much for help for support
I really get more benefit from that forum so much
and i learn so much .
sometimes i ask questions to get more efficient answer to my questions
OK i understand about what you talk above and i will improve my self
only one question i need answer for it
you confuse because task is trivial or Im not try .
please tell me i lookup to Learn from my wrong to not repeat again ?

OriginalGriff 1-Apr-20 4:01am

"I really get more benefit from that forum so much
and i learn so much ."
No, you don't. That's the whole problem, and something that has been raised with you repeatedly.
You aren't learning. You aren't even trying to learn, because you are asking the same basic questions over, and over, and over again.
And there is no point at all in wasting our time trying to help someone who doesn't want to learn at all, just get his current problem fixed so he can move on to the next.

ahmed_sa 1-Apr-20 4:03am

OK i understand

MadMyche 1-Apr-20 8:21am

You can also include me in the +5 club.

It is also frustrating that when a question does get answered, you do not say Thank You or otherwise acknowledge it.
The time that goes into deciphering what you have written and formatting the sloppy code so that I can try to help actually takes longer than the actual code issue. If this was billable labor, the cost in my time (and my employer) is literally thousands of dollars.

Maciej Los · Accepted Answer · 2020-04-01T10:38:00

Take a look at example:

SQL

DECLARE @Donecode TABLE(FunctionId int, FunctionName nvarchar(50))
insert into @Donecode(FunctionId, FunctionName)
values(1,'Lifecycle'),(2,'Rohs'),(3,'Reach'),(4,'FMD'),(5,'Parametric'),
(6,'Package'),(7,'IntroductionDate'),(8,'MFG'),(9,'Qualification')

DECLARE @filedetails  TABLE(FileID   int, DoneCode nvarchar(50))
insert into @filedetails (FileID,DoneCode) 
values (3301,'101011111'), (3301,'101101111'), (3301,'101001000')

;WITH CTE AS
(
	--initial part
	SELECT FileID, 1 AS FunctionId, CONVERT(INT, LEFT(DoneCode, 1)) AS MyValue, RIGHT(DoneCode, LEN(DoneCode)-1) AS Remainder, ROW_NUMBER() OVER(ORDER BY FileID) AS RowNo
	FROM @filedetails
	--recursive part
	UNION ALL
	SELECT FileID, FunctionId + 1 AS FunctionId, CONVERT(INT, LEFT(Remainder, 1)) AS MyValue, RIGHT(Remainder, LEN(Remainder)-1) AS Remainder, RowNo AS RowNo
	FROM CTE
	WHERE LEN(Remainder)>0
)
SELECT dc.FunctionName, SUM(CASE WHEN c.MyValue = 1 THEN 1 ELSE 0 END) AS CountDoneCode, SUM(CASE WHEN c.MyValue = 0 THEN 1 ELSE 0 END) AS CountNotDoneCode
FROM CTE c INNER JOIN @Donecode dc ON c.FunctionId = dc.FunctionId 
GROUP BY dc.FunctionId, dc.FunctionName
ORDER BY dc.FunctionId

I've used CTE[^] to split DoneCode into parts.

Result:

FunctionName	CountDoneCode	CountNotDoneCode
Lifecycle			3			0
Rohs				0			3
Reach				3			0
FMD					1			2
Parametric			1			2
Package				3			0
IntroductionDate	2			1
MFG					2			1
Qualification		2			1

Good luck!

BTW: your database structure is... invalid.