2

I have this SQL query which is causing me to struggle a little bit.

SELECT c.Threat_Type, COUNT(*) FROM BORE.NormalToilet c
   LEFT JOIN BORE.EnragedToilet p on p.Toilet_ID = c.Toilet_ID
GROUP BY c.Threat_Type

Here's the output: 

+--------------+--------+
| Threat Type  |  Count |
+--------------+--------+
| Portable     |   26   |
| Japanese     |    1   |
| Toilet       |    1   |
| Assassin     |    3   |
+--------------+--------+

Now here's the problem with this query:

  1. There are, correctly, 26 vulnerabilities in our portable toilet firmware.
  2. There is, incorrectly listed, 1 vulnerability in our Japanese toilet... there are no vulnerabilities, only requested features. This should say 0, not 1.
  3. Same problem as above: Toilet features have no vulnerabilities.

If I try an INNER JOIN, it doesn't include the fields with 1. This is the correct behavior (as there are no matches), however, I want it to say 0 instead of 1. Using a LEFT JOIN, it just says 1, which is heinously incorrect.

What am I doing wrong?

Improve this question
5
  • 4
    You want count(p.Toilet_ID) Commented Dec 15, 2015 at 16:07
  • 3
    when you use left join the row would contain a null value in one of the columns. As you are doing a count(*) it would count the entire row. so you get 1. Use count(somecolumnname) instead/. Commented Dec 15, 2015 at 16:07
  • 1
    I'm more concerned with how an enraged portable toilet has 26 vulnerabilities. Or what an assassin toilet might be... Commented Dec 15, 2015 at 16:09
  • Can't believe I missed that... thanks. Commented Dec 15, 2015 at 16:09
  • 1
    @a_horse_with_no_name or vkp, if you're interested in getting a correct answer checked, I will accept it. Commented Dec 15, 2015 at 16:15

1 Answer 1

Reset to default
1

Credit to a_horse_with_no_name and vkp in the comments. If they do end up posting their own answer, feel free to vote to delete this answer.

Instead of count(*) which counts rows, you want count(p.Toilet_ID), which only counts the rows where p.Toilet_ID is not null.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.