combine two tables with combining select statemens

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

combine two tables with combining select statemens

Author

Topic

DkMCCAMMON
Starting Member

3 Posts

Posted - 2013-05-23 : 15:43:04

OK, so I have two different tables that are like such

table name: serial_data

serial partnumber
123 555
456 777

the next table is: leak_tests

serial_number passed
123 0
456 1

So what I am trying to do is get a count of partnumbers that have failed, and a count of partnumbers that have passed, and then a count of all part numbers.

So far I can get all three, but not at the same time in the same query. I have tried the following:

try 1:
SELECT COUNT("0") FAIL, COUNT("1") PASS, COUNT("0" AND "1") TOTAL, PARTNUMBER
FROM LEAK_TESTS
INNER JOIN SERIAL_DATA
ON
SERIAL_DATA.SERIAL=LEAK_TESTS.SERIAL_NUMBER
WHERE TESTED_ON >= "2012-01-01"
GROUP BY SERIAL_NUMBER
ORDER BY FAIL DESC

try 2:
SELECT DISTINCT serial_data.partnumber,

(SELECT COUNT(*) AS PASS
FROM serial_data
INNER JOIN leak_tests ON leak_tests.serial_number=serial_data.serial
WHERE tested_on >= "2012-02-18" AND PASSED = "1"),

(SELECT COUNT(*) AS FAIL
FROM serial_data
INNER JOIN leak_tests ON leak_tests.serial_number=serial_data.serial
WHERE tested_on >= "2012-02-18" AND PASSED = "0")

FROM SERIAL_DATA
ORDER BY PARTNUMBER

here are the queries individually, I tried a union on them and that didn't work either.

SELECT SERIAL_DATA.PARTNUMBER, COUNT(PASSED) TOTAL
FROM SERIAL_DATA
INNER JOIN LEAK_TESTS ON LEAK_TESTS.SERIAL_NUMBER=SERIAL_DATA.SERIAL
WHERE TESTED_ON >="2012-01-01"
GROUP BY PARTNUMBER

SELECT serial_data.partnumber, COUNT(*) FAIL
FROM serial_data
INNER JOIN leak_tests ON leak_tests.serial_number=serial_data.serial
WHERE tested_on >= "2012-01-01" AND PASSED = "1"
GROUP BY partnumber

SELECT serial_data.partnumber, COUNT(*) PASS
FROM serial_data
INNER JOIN leak_tests ON leak_tests.serial_number=serial_data.serial
WHERE tested_on >= "2012-01-01" AND PASSED = "0"
GROUP BY partnumber

so basically I am trying to get these three results to display with one query, and eventually I want to take the total number and divide by the failing number to see what the percentage of parts fail, by partnumber.

Anybody have any suggestions or any ideas, or if this can even be done in the same query?

Thank you.

Darin K. McCammon

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-23 : 15:48:37

This perhpas?

SELECT
	sd.partnumber,
	SUM(CASE WHEN lt.passed = 0 THEN 1 ELSE 0 END) AS Failed,
	SUM(CASE WHEN lt.passed = 1 THEN 1 ELSE 0 END) AS Passed,
	SUM(CASE WHEN lt.passed IN (1,0) THEN 1 ELSE 0 END) AS Total
FROM
	serial_data sd
	INNER JOIN leak_tests lt ON 
		lt.serial_number = serial_data.serial
GROUP BY
	sd.part_number;

DkMCCAMMON
Starting Member

3 Posts

Posted - 2013-05-23 : 15:52:56

I tried that and got this result

Error: null, message from server: "Unknown column 'serial_data.serial' in 'on clause'"
SQLState: 42S22
ErrorCode: 1054

serial_data.serial is a known column tho.

quote:

Originally posted by James K

This perhpas?

SELECT
	sd.partnumber,
	SUM(CASE WHEN lt.passed = 0 THEN 1 ELSE 0 END) AS Failed,
	SUM(CASE WHEN lt.passed = 1 THEN 1 ELSE 0 END) AS Passed,
	SUM(CASE WHEN lt.passed IN (1,0) THEN 1 ELSE 0 END) AS Total
FROM
	serial_data sd
	INNER JOIN leak_tests lt ON 
		lt.serial_number = serial_data.serial
GROUP BY
	sd.part_number;

Darin K. McCammon

DkMCCAMMON
Starting Member

3 Posts

Posted - 2013-05-23 : 15:57:51

I changed it up a bit like this, and buddy it works, thank you so much

SELECT
sd.partnumber,
SUM(CASE WHEN lt.passed = 0 THEN 1 ELSE 0 END) AS Failed,
SUM(CASE WHEN lt.passed = 1 THEN 1 ELSE 0 END) AS Pass,
SUM(CASE WHEN lt.passed IN (1,0) THEN 1 ELSE 0 END) AS Total
FROM
serial_data sd
INNER JOIN leak_tests lt ON
lt.serial_number = sd.serial
GROUP BY
sd.partnumber;

quote:
Originally posted by DkMCCAMMON

I tried that and got this result

Error: null, message from server: "Unknown column 'serial_data.serial' in 'on clause'"
SQLState: 42S22
ErrorCode: 1054

serial_data.serial is a known column tho.

quote:
Originally posted by James K

This perhpas?
SELECT
sd.partnumber,
SUM(CASE WHEN lt.passed = 0 THEN 1 ELSE 0 END) AS Failed,
SUM(CASE WHEN lt.passed = 1 THEN 1 ELSE 0 END) AS Passed,
SUM(CASE WHEN lt.passed IN (1,0) THEN 1 ELSE 0 END) AS Total
FROM
serial_data sd
INNER JOIN leak_tests lt ON
lt.serial_number = serial_data.serial
GROUP BY
sd.part_number;

Darin K. McCammon

Darin K. McCammon

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-23 : 18:03:41

Yes, my mistake - sorry about that. Glad you figured it out though.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources