Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
In this query I need get Only one record If Full Name is Same for Batchstatsustestext(Coding, Coding review) Minus the Transaction count and show only one line Item
Please help to buld query

What I have tried:

I have use Below query in sql server

SELECT RSNNo, FullName, [Transaction Count], Stage, BatchStatusText
FROM (SELECT RSNNo, FullName, [Transaction Count], Stage, BatchStatusText, CONVERT(date, [Completion Date], 103) AS [Completion Date], BatchID, InvoiceID, CompanyCode, UserName, DocID,
NUMBER
FROM qryTransactionLevelDetails Where BatchID =15515

GROUP BY RSNNo, FullName, [Transaction Count], Stage, BatchStatusText, CONVERT(date, [Completion Date], 103), BatchID, InvoiceID, CompanyCode, UserName, DocID, NUMBER)
AS tbl1 Where (tbl1.Stage=6 OR tbl1.Stage=7)


Result of same as :

RSNNo FullName Transaction Count Stage BatchStatusText
14637 ASZulpe 8 6 Coding
14637 ASZulpe 8 7 Coding Review
Posted
Updated 14-Dec-17 22:20pm
Comments
Santosh kumar Pithani 16-Dec-17 4:27am    
can you give sample example on your question with expected output?

1 solution

The problem is that you are grouping by absolutely everything: and each time you add a GROUP BY clause, it adds extra rows to your output.
If you had this data:
A   B   C
0   0   0
0   0   1
0   1   0
0   1   1
1   0   0
1   0   1
1   1   0
1   1   1
And you did this:
SQL
SELECT A, SUM(B) AS x, SUM(C) AS y FROM MyTable GROUP BY A

Then you get two rows:
A   x   y
0   2   2
1   2   2
But if you add a second column to your group:
SQL
SELECT A, SUM(B) AS x, SUM(C) AS y FROM MyTable GROUP BY A, B

Then you get four rows:
A   x   y
0   0   1
1   0   1
0   2   1
1   2   1
Because the addition of B to the grouping adds uniqueness to the rows.
Each time you add a clause, you increase the number of rows.
And you have added everything because you can't return a column in a SELECT ... GROUP BY statement unless it is a column listed in the GROUP BY, or in a aggregate function.

Have a look here: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^] then sit down and think about your data and exactly what you are trying to extract and how that relates to tyour table layout and content.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900