问题:
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 |
+-----------+---------+