Trying to group a SQL query?

This post is very similar to a previous post and yet this one is not working and I have NO idea why. What I am seeking to do is get a a "Count" of a particular column. There are two records in the Database, however, I only wish to return 1 record but store the count of 2 in a returned column called "Total Runs". Below is my SQL Query. The attached screenshot is what is currently being returned.

DECLARE @creepRuns		INT=NULL
DECLARE @creepStation	VARCHAR(50)=NULL
DECLARE @repeatRuns		INT=NULL
DECLARE @repeatStation	VARCHAR(50)=NULL

	SELECT	@creepRuns = MAX(E.runNO),
			@creepStation = G.stationName,
			@repeatRuns = MAX(F.runNO),
			@repeatStation = H.stationName
	  FROM  HEADER_Item A LEFT JOIN TEST_HEADER_Load B ON A.main_ID = B.main_ID
			INNER JOIN HEADER_Main D ON A.main_ID = D.main_ID
			LEFT JOIN TEST_HEADER_Creep E ON A.main_ID = E.main_ID
			LEFT JOIN TEST_HEADER_Repeatability F ON A.main_ID = F.main_ID
			LEFT JOIN LKUP_TestStations G ON G.station_ID = E.station_ID
			LEFT JOIN LKUP_TestStations H ON H.station_ID = F.station_ID
	 WHERE	A.serialNO = '545446'
	 GROUP BY G.stationName,H.stationName
	 
	SELECT	A.serialNO AS 'S/N'			--0
			,B.main_ID AS 'Test ID'		--1
			,CASE 
				WHEN D.profileType = 'R' 
					THEN 'Recent'
				WHEN D.profileType = 'D'
					THEN 'Default'
				ELSE	
					''
			 END AS 'Profile'	--2
			,COUNT(B.main_ID) AS 'Total Run(s)'	--3
			--,CASE WHEN B.tensionRuns >= B.compressionRuns THEN B.tensionRuns ELSE B.compressionRuns END AS 'Total Run(s)'	--3
			,C.commonName AS 'Tech Name'	--4
			,D.dateCreated AS 'Calibration Date'	--5
			,COALESCE(G.stationName, @creepStation, @repeatStation, '') AS 'Test Station'	--6
			,A.itemNO AS 'Item #'	--7
			,A.modelNO AS 'Model'	--8
			,B.[load] AS 'Capacity'	--9
			,B.endTime AS 'Ending Time'	--10
			,CASE WHEN CONVERT(VARCHAR,D.loadTEST) <> '0' THEN 'Load,' ELSE '' END
			 + CASE WHEN CONVERT(VARCHAR,D.resistanceTEST) <> '0' THEN 'Electrical,' ELSE '' END
			 + CASE WHEN CONVERT(VARCHAR,D.excitationTEST) <> '0' THEN 'Excitation,' ELSE '' END
			 + CASE WHEN CONVERT(VARCHAR,D.shuntTEST) <> '0' THEN 'Shunt,' ELSE '' END
			 + CASE WHEN CONVERT(VARCHAR,D.creepTEST) <> '0' THEN 'Creep(' + CONVERT(VARCHAR, @creepRuns) + '),' ELSE '' END
			 + CASE WHEN CONVERT(VARCHAR,D.recoveryTEST) <> '0' THEN 'Recovery,' ELSE '' END
			 + CASE WHEN CONVERT(VARCHAR,D.repeatabilityTEST) <> '0' THEN 'Repeatability(' + CONVERT(VARCHAR, @repeatRuns) + '),' ELSE '' END
			 + CASE WHEN CONVERT(VARCHAR,D.zeroBalanceTEST) <> '0' THEN 'FF/Zero,' ELSE '' END AS 'Ordered Tests'	--11
			,B.channel_ID	--12
			,B.direction1_ID	--13
			,B.angle_ID		--14
			,D.profileType	--15
			,B.direction2_ID	--16
	  FROM  HEADER_Item A INNER JOIN TEST_HEADER_Load B ON A.main_ID = B.main_ID
			LEFT JOIN LKUP_UserAccounts C ON A.createdBy_ID = C.userAccount_ID
			INNER JOIN HEADER_Main D ON A.main_ID = D.main_ID
			LEFT JOIN LKUP_TestStations G ON B.station_ID = G.station_ID
	 WHERE	A.serialNO = '545446'
	 GROUP BY B.main_ID,A.serialNO,C.commonName,A.itemNO,A.modelNO,B.[load],B.endTime,B.channel_ID,B.direction1_ID,B.angle_ID,B.direction2_ID,
			D.dateCreated,G.stationName,D.loadTEST,D.resistanceTEST,D.excitationTEST,D.shuntTEST,D.creepTEST,D.recoveryTEST,D.repeatabilityTEST,
			D.profileType,D.zeroBalanceTEST
	ORDER BY D.dateCreated DESC

Open in new window


Thanks!
Screenshot.jpg
BlakeMcKennaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you post those two records so we can see the differences on both?
0
BlakeMcKennaAuthor Commented:
They're in the screenshot...
0
BlakeMcKennaAuthor Commented:
Attached is a screenshot of each separate rows for each of the tables.
Screenshot.jpg
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, didn't see that.
Do you really need the endTime to be returned? If you take that field you'll have the information as you want.
0
BlakeMcKennaAuthor Commented:
I do need that column. However, I just tried removing the endTime from the Select clause as well as the Group By and there was no change in the result.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
The screenshot doesn't let see more columns. You need to see all columns that have different values on it.
0
BlakeMcKennaAuthor Commented:
Here is all the records in their entirety in a spreadsheet.
TestData.xlsx
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can see by yourself. These columns has different values and that's why can't be grouped:
runNO, tensionRuns, direction2_ID, load_ID, loadUnitType_ID, loadUnit_ID, outputDecimalPlaces, maxOutput, maxNonlinearity, startTime, endTime, dateCreated, dateUpdated
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BlakeMcKennaAuthor Commented:
So your saying that every like column in each table needs to be identical?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes. That's what GROUP BY means (join all equal rows and perform an aggregate operation on desired columns).
That's why you are having only COUNT()=1 because for the engine the rows are different.
0
BlakeMcKennaAuthor Commented:
OK,

Thanks for your help Vitor!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.