Getting 2 result columns from SQL query-CodePudding

I've tried to make an SQL query to get 2 result columns from 1 query. To elaborate:

This is my query:

SELECT Name, AVG(DATEDIFF(day,[Open Date],[Close Date])) 
FROM Inventory Where Tested ='Yes' AND [Close Date] IS NOT NULL AND [Open Date] IS NOT NULL 
GROUP BY Name

My result:

My desired result: When also Where Tested = 'No'

How to write an SQL query with Where Tested = 'Yes' and Where Tested = 'No'? Where Tested = 'Yes' is Column 1 result and Tested = 'No' is Column 2 result.

Database:SQL server 2019

CodePudding user response：

Out of three columns if you trying to get two columns with its values, you have to specify those columns in your code. Example: SELECT column1, column2 FROM table_name

But if you want to find all the columns Example: SELECT * FROM table_name

CodePudding user response：

It's hard to say exactly what you want, but maybe you could use a couple of subqueries?

SELECT Name, 
AVG(select DATEDIFF(i2.day,i2.[Open Date],i2.[Close Date]) from inventory i2 where i2.name = i.name and i2.Tested = 'Yes') as "Tested", 
AVG(select DATEDIFF(i3.day,i3.[Open Date],i3.[Close Date]) from inventory i3 where i3.name = i.name and i3.Tested = 'No') as "Not Tested" 
FROM Inventory i
Where [Close Date] IS NOT NULL AND [Open Date] IS NOT NULL 
GROUP BY Name

CodePudding user response：

EJC answer with subqueries should already probably help with what you need, but just wanted to share some other options to achieve this:

Joining two separate queries

SELECT tested.Name, tested.TestedAVG, not_tested.NotTestedAVG FROM

(SELECT Name, AVG(DATEDIFF(day,[Open Date],[Close Date])) as "TestedAVG"
FROM Inventory 
WHERE Tested ='Yes' AND [Close Date] IS NOT NULL AND [Open Date] IS NOT NULL 
GROUP BY Name) tested

FULL JOIN

(SELECT Name, AVG(DATEDIFF(day,[Open Date],[Close Date])) as "NotTestedAVG"
FROM Inventory 
WHERE Tested ='No' AND [Close Date] IS NOT NULL AND [Open Date] IS NOT NULL 
GROUP BY Name) not_tested

ON tested.Name = not_tested.Name

Using case

SELECT Name, 
AVG(CASE WHEN Tested ='Yes' THEN DATEDIFF(day,[Open Date],[Close Date]) END) as [Tested AVG],
AVG(CASE WHEN Tested ='No' THEN DATEDIFF(day,[Open Date],[Close Date]) END) as [Not Tested AVG]
FROM Inventory 
WHERE [Close Date] IS NOT NULL AND [Open Date] IS NOT NULL 
GROUP BY Name

Adding as group by

If you don't need to have it as a separate column, the easiest way would be to add a group by the Tested column as well.

SELECT Name, AVG(DATEDIFF(day,[Open Date],[Close Date])) as "AVG"
FROM Inventory
WHERE [Close Date] IS NOT NULL AND [Open Date] IS NOT NULL 
GROUP BY Name, Tested

Then you'll get this structure:

Name	Tested	AVG
aTPO	Yes	56
aTPO	No	50