Access SQL query with JOIN and a COUNT statement not counting anything

问题: I have two tables in MS Access. One for planets and one for the type of star it orbits. I want to get a count of the planets per star type...so something like: +-------...

问题:

I have two tables in MS Access. One for planets and one for the type of star it orbits.

I want to get a count of the planets per star type...so something like:

+----------+--------------------+
| StarType | PlanetsPerStarType |
+----------+--------------------+
| A        |                  4 |
| B        |                  1 |
| C        |                  7 |
+----------+--------------------+

So I wrote this SQL query:

SELECT StarType, COUNT(PlanetName) AS PlanetsPerStarType
FROM Planets AS p
LEFT JOIN StarClass AS s ON p.sid = s.sid
GROUP BY starType, PlanetName

But it just lists all the planets and a 1 for all the star types, it doesn't really count at all.

What could I be doing wrong?


回答1:

By grouping by the starType and the PlanetName, the count is returning the number of records in each starType & PlanetName combination, which, unless you have more than one planet of the same name orbiting your star, will always be one.

For example, given the data:

+-----------+------------------+
| StarType  |    PlanetName    |
+-----------+------------------+
| G2V       | Mars             |
| G2V       | Earth            |
| G2V       | Venus            |
| Red Dwarf | Omicron Persei 8 |
| Red Dwarf | Vergon 6         |
+-----------+------------------+

Grouping by StarType and PlanetName will yield exactly the same data, since there are no repeating StarType and PlanetName combinations which would be consolidated into a group.

As such, the SQL code:

select t.StarType, count(t.PlanetName) as Planets
from YourTable t
group by t.StarType, t.PlanetName

Would yield:

+-----------+---------+
| StarType  | Planets |
+-----------+---------+
| G2V       |       1 |
| G2V       |       1 |
| G2V       |       1 |
| Red Dwarf |       1 |
| Red Dwarf |       1 |
+-----------+---------+

Since each group contains exactly one record.

If, instead, we group only by StarType, the Count aggregate function will return the number of records associated with each StarType:

select t.StarType, count(t.PlanetName) as Planets
from YourTable t
group by t.StarType
+-----------+---------+
| StarType  | Planets |
+-----------+---------+
| G2V       |       3 |
| Red Dwarf |       2 |
+-----------+---------+
  • 发表于 2019-03-30 03:14
  • 阅读 ( 185 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除