Getting 2 result columns from SQL query

Question

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

Please share a minimal reproducible example of your database table, including schema and sample data. In addition, please specify what database provider you're using. — gunr2171
– gunr2171, Commented Sep 7, 2022 at 13:23

re_arg · Accepted Answer · 2022-09-07 16:36:05Z

2

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

edited Sep 7, 2022 at 16:36

answered Sep 7, 2022 at 16:34

re_arg

1567 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

EJC Over a year ago

Your option 2 is better than what I wrote and more along the lines of what I was thinking. It's been a while since I've done any sql.

baxius Over a year ago

Thank you. All of your answers work perfectly.

EJC · Accepted Answer · 2022-09-07 13:36:02Z

1

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

answered Sep 7, 2022 at 13:36

EJC

2,1715 gold badges22 silver badges34 bronze badges

Comments

Obsidian · Accepted Answer · 2022-09-13 13:46:43Z

0

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

edited Sep 13, 2022 at 13:46

Obsidian

3,9308 gold badges20 silver badges33 bronze badges

answered Sep 7, 2022 at 13:35

Ahnibaba

11 bronze badge

Collectives™ on Stack Overflow

Getting 2 result columns from SQL query

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related