Need help with a SQL Query?

I have a SQL Query in which I need to tweak it to return 1 row instead of 2 rows. The attached image shows the current scenario.

Below is the SQL Query that produces the results in the image.

	SELECT	A.serialNO AS 'S/N'			--0
			,A.main_ID AS 'Test ID'		--1
			,CASE 
				WHEN profileType = 'R' 
					THEN 'Recent'
				WHEN profileType = 'D'
					THEN 'Default'
				ELSE	
					''
			 END AS 'Profile'	--2
			,B.runNO AS 'Total Run(s)'	--3
			,C.commonName AS 'Tech Name'	--4
			,ISNULL(B.dateCreated,D.dateCreated) AS 'Calibration Date'	--5
			,COALESCE(G.stationName, H.stationName, I.stationName, '') 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, E.numberOfRuns) + '),' ELSE '' END
			 + CASE WHEN CONVERT(VARCHAR,D.recoveryTEST) <> '0' THEN 'Recovery,' ELSE '' END
			 + CASE WHEN CONVERT(VARCHAR,D.repeatabilityTEST) <> '0' THEN 'Repeatability(' + CONVERT(VARCHAR, F.numberOfRuns) + '),' 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 LEFT 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 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 B.station_ID = G.station_ID
			LEFT JOIN LKUP_TestStations H ON E.station_ID = H.station_ID
			LEFT JOIN LKUP_TestStations I ON F.station_ID = I.station_ID
	 WHERE	A.serialNO = @serialNO
	ORDER BY B.dateCreated DESC

Open in new window


The number of rows in the DataGridView (DGV) in the attached image is based off of the number of rows in the table "TEST_HEADER_Load" alias B. So essentially what I need is to get a count of the rows in "B" and just return one row in the DGV.

The Primary Key for the table "TEST_HEADER_Load" consists of 6 columns:  main_ID, seqNO (will always be 1), channel_ID, direction_ID, angle_ID, runNO.

The "Total Run(s)" column in the DGV is directly proportionate with the "runNO" column. This column, "runNO", is the column that is incremented and has no limit but rarely exceeds 10.

 I may have not provided enough information for this. Please feel free to ask for more info.

Thanks!
ExistingTests.JPG
BlakeMcKennaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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:
You just need a GROUP BY clause and a SUM on total:
	SELECT	A.serialNO AS 'S/N'			--0
			,A.main_ID AS 'Test ID'		--1
			,CASE 
				WHEN profileType = 'R' 
					THEN 'Recent'
				WHEN profileType = 'D'
					THEN 'Default'
				ELSE	
					''
			 END AS 'Profile'	--2
			,SUM(B.runNO) AS 'Total Run(s)'	--3
			,C.commonName AS 'Tech Name'	--4
			,ISNULL(B.dateCreated,D.dateCreated) AS 'Calibration Date'	--5
			,COALESCE(G.stationName, H.stationName, I.stationName, '') 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, E.numberOfRuns) + '),' ELSE '' END
			 + CASE WHEN CONVERT(VARCHAR,D.recoveryTEST) <> '0' THEN 'Recovery,' ELSE '' END
			 + CASE WHEN CONVERT(VARCHAR,D.repeatabilityTEST) <> '0' THEN 'Repeatability(' + CONVERT(VARCHAR, F.numberOfRuns) + '),' 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 LEFT 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 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 B.station_ID = G.station_ID
			LEFT JOIN LKUP_TestStations H ON E.station_ID = H.station_ID
			LEFT JOIN LKUP_TestStations I ON F.station_ID = I.station_ID
	 WHERE	A.serialNO = @serialNO
GROUP BY A.serialNO AS 'S/N'			--0
			,A.main_ID AS 'Test ID'		--1
			,CASE 
				WHEN profileType = 'R' 
					THEN 'Recent'
				WHEN profileType = 'D'
					THEN 'Default'
				ELSE	
					''
			 END AS 'Profile'	--2
			,C.commonName AS 'Tech Name'	--4
			,ISNULL(B.dateCreated,D.dateCreated) AS 'Calibration Date'	--5
			,COALESCE(G.stationName, H.stationName, I.stationName, '') 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, E.numberOfRuns) + '),' ELSE '' END
			 + CASE WHEN CONVERT(VARCHAR,D.recoveryTEST) <> '0' THEN 'Recovery,' ELSE '' END
			 + CASE WHEN CONVERT(VARCHAR,D.repeatabilityTEST) <> '0' THEN 'Repeatability(' + CONVERT(VARCHAR, F.numberOfRuns) + '),' 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
	ORDER BY B.dateCreated DESC

Open in new window

0
BlakeMcKennaAuthor Commented:
But that would require using every column that is selected to be put in the GROUP BY clause...correct?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, but it's the only way if you want to use an aggregate function (SUM, COUNT, AVG, MIN, MAX).
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Scott PletcherSenior DBACommented:
You're getting a lot of detail columns -- load, endtime, etc. -- from the B table.  Do you want to show only one row from the B table in the result with the total number of runs?  If so, which values do you want to show for the detail columns -- the first row, the min/max, what?
0
BlakeMcKennaAuthor Commented:
Scott,

Your correct. I only want to show 1 row from the B table but the "load" and "endTime" columns will be the same for however many rows are in the B table.
0
Scott PletcherSenior DBACommented:
SELECT      A.serialNO AS 'S/N'                  --0
                  ,A.main_ID AS 'Test ID'            --1
                  ,CASE
                        WHEN profileType = 'R'
                              THEN 'Recent'
                        WHEN profileType = 'D'
                              THEN 'Default'
                        ELSE      
                              ''
                   END AS 'Profile'      --2
                  ,B.[Total Run(s)]      --3
                  ,C.commonName AS 'Tech Name'      --4
                  ,ISNULL(B.dateCreated,D.dateCreated) AS 'Calibration Date'      --5
                  ,COALESCE(G.stationName, H.stationName, I.stationName, '') AS 'Test Station'      --6
                  ,A.itemNO AS 'Item #'      --7
                  ,A.modelNO AS 'Model'      --8
                  ,B.Capacity      --9
                  ,B.[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, E.numberOfRuns) + '),' ELSE '' END
                   + CASE WHEN CONVERT(VARCHAR,D.recoveryTEST) <> '0' THEN 'Recovery,' ELSE '' END
                   + CASE WHEN CONVERT(VARCHAR,D.repeatabilityTEST) <> '0' THEN 'Repeatability(' + CONVERT(VARCHAR, F.numberOfRuns) + '),' 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
              LEFT JOIN (
                  SELECT
                          channel_ID      --12
                          ,direction1_ID      --13
                          ,angle_ID            --14
                          ,profileType      --15
                          ,direction2_ID      --16
                          ,[load] AS 'Capacity'      --9
                          ,endTime AS 'Ending Time'      --10
                          ,SUM(runNO) AS 'Total Run(s)'
                  FROM TEST_HEADER_Load
                  GROUP BY
                          channel_ID      --12
                          ,direction1_ID      --13
                          ,angle_ID            --14
                          ,profileType      --15
                          ,direction2_ID      --16
                          ,[load]      --9
                          ,endTime --10
              ) AS 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 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 B.station_ID = G.station_ID
                  LEFT JOIN LKUP_TestStations H ON E.station_ID = H.station_ID
                  LEFT JOIN LKUP_TestStations I ON F.station_ID = I.station_ID
       WHERE      A.serialNO = @serialNO
      ORDER BY B.dateCreated DESC
0
BlakeMcKennaAuthor Commented:
Scott,

I tried your code and it gave me errors when I tried to run it. However, I did get it working by adding a little more code to the Stored Procedure (this query was part of a SP).

I have attached a screenshot of the new code.

Here is what I came up with and it's not clean code. If you notice, I have 3 columns commented out and I need to have these included in the query. Is there any way I can include those 3 columns in the way I'm using the variables? Is there a way to clean up those "SET" statements? Consolidate them or just make it look cleaner?

Thanks,
Query.JPG
0
PortletPaulEE Topic AdvisorCommented:
Any reason why you chose to image that query instead of making is a code block? (please don't use images of code)

& It looks more like you REMOVED stuff instead, so you no longer have any columns from "B".

Anyway, with that sp in the image you don't have any unwanted rows?
0
PortletPaulEE Topic AdvisorCommented:
Perhaps this will assist.
Scott had included a column from alias "D" in the subquery designed to replace "B" (that would have produced an error) a simple oversight in code we cannot test.
SELECT
    A.serialNO                                                                                  AS 'S/N'               --0
  , A.main_ID                                                                                   AS 'Test ID'           --1
  , CASE WHEN profileType = 'R' THEN 'Recent' WHEN profileType = 'D' THEN 'Default' ELSE '' END AS 'Profile'           --2
  , B.[Total Run(s)]      --3
  , C.commonName                                                                                AS 'Tech Name'         --4
  , ISNULL(B.dateCreated, D.dateCreated)                                                        AS 'Calibration Date'  --5
  , COALESCE(G.stationName, H.stationName, I.stationName, '')                                   AS 'Test Station'      --6
  , A.itemNO                                                                                    AS 'Item #'            --7
  , A.modelNO                                                                                   AS 'Model'              --8
  , B.Capacity         --9
  , B.[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, E.numberOfRuns) + '),' ELSE '' END
    + CASE WHEN CONVERT(varchar, D.recoveryTEST) <> '0' THEN 'Recovery,' ELSE '' END
    + CASE WHEN CONVERT(varchar, D.repeatabilityTEST) <> '0'
                    THEN 'Repeatability(' + CONVERT(varchar, F.numberOfRuns) + '),' 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
LEFT JOIN (
            SELECT
                COUNT(*)      AS 'Total Run(s)'  --3
              , [load]        AS 'Capacity'      --9
              , endTime       AS 'Ending Time'   --10
              , channel_ID                       --12
              , direction1_ID                    --13
              , angle_ID                         --14
              , direction2_ID                    --16
            FROM TEST_HEADER_Load
            GROUP BY
                [load]             --9
              , endTime            --10
              , channel_ID         --12
              , direction1_ID      --13
              , angle_ID           --14
              , direction2_ID      --16
        ) AS 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 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 B.station_ID = G.station_ID
LEFT JOIN LKUP_TestStations H ON E.station_ID = H.station_ID
LEFT JOIN LKUP_TestStations I ON F.station_ID = I.station_ID
WHERE A.serialNO = @serialNO
ORDER BY
    B.dateCreated DESC

Open in new window


If this errors please provide us with the error code and message.

Oh, & I have used COUNT(*) instead of SUM(RunNo) as I'm assuming you only need a count.
0
PortletPaulEE Topic AdvisorCommented:
I hope this illustrates why images of code are frustrating.

Here is my solution:image-of-code.PNG
0
BlakeMcKennaAuthor Commented:
PortlePaul,

Thanks for the code. I ran it and I did get a few errors. I used a screenshot so you could see exactly what I see.

Thanks!
Screenshot.jpg
0
BlakeMcKennaAuthor Commented:
What do you mean by images of code? You lost me there...
0
BlakeMcKennaAuthor Commented:
Nevermind...I forgot I posted that image of the code.
0
PortletPaulEE Topic AdvisorCommented:
You most probably could work out those errors, both main_id and date_created were missing from the subquery for alias "B". Now I have put both in to that subquery I can't be certain that you won't end up with unwanted rows. Hopefully not.

I won't use an image:)
SELECT
    A.serialNO                                                                                  AS 'S/N'               --0
  , A.main_ID                                                                                   AS 'Test ID'           --1
  , CASE WHEN profileType = 'R' THEN 'Recent' WHEN profileType = 'D' THEN 'Default' ELSE '' END AS 'Profile'           --2
  , B.[Total Run(s)]      --3
  , C.commonName                                                                                AS 'Tech Name'         --4
  , ISNULL(B.dateCreated, D.dateCreated)                                                        AS 'Calibration Date'  --5
  , COALESCE(G.stationName, H.stationName, I.stationName, '')                                   AS 'Test Station'      --6
  , A.itemNO                                                                                    AS 'Item #'            --7
  , A.modelNO                                                                                   AS 'Model'              --8
  , B.Capacity         --9
  , B.[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, E.numberOfRuns) + '),' ELSE '' END
    + CASE WHEN CONVERT(varchar, D.recoveryTEST) <> '0' THEN 'Recovery,' ELSE '' END
    + CASE WHEN CONVERT(varchar, D.repeatabilityTEST) <> '0'
                    THEN 'Repeatability(' + CONVERT(varchar, F.numberOfRuns) + '),' 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
LEFT JOIN (
            SELECT
                COUNT(*)      AS 'Total Run(s)'  --3
              , main_ID                          --1
              , dateCreated                      --5
              , [load]        AS 'Capacity'      --9
              , endTime       AS 'Ending Time'   --10
              , channel_ID                       --12
              , direction1_ID                    --13
              , angle_ID                         --14
              , direction2_ID                    --16
            FROM TEST_HEADER_Load
            GROUP BY
                main_ID            --1
              , dateCreated        --5
              , [load]             --9
              , endTime            --10
              , channel_ID         --12
              , direction1_ID      --13
              , angle_ID           --14
              , direction2_ID      --16
        ) AS 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 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 B.station_ID = G.station_ID
LEFT JOIN LKUP_TestStations H ON E.station_ID = H.station_ID
LEFT JOIN LKUP_TestStations I ON F.station_ID = I.station_ID
WHERE A.serialNO = @serialNO
ORDER BY
    B.dateCreated DESC

Open in new window

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:
There was just one error, the "station_ID" wasn't in the subquery but that was easy!

Thanks PortlePaul!
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.