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?
 
PortletPaulfreelancerCommented:
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
 
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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

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

All Courses

From novice to tech pro — start learning today.