MySQL Forums
Forum List  »  SQLite

SELECT, COUNT and Inner Join
Posted by: Geir Anthonsen
Date: November 16, 2019 06:21AM

I am not to familiar with databases, because I work with wifi networks.

I have imported a text file with all the issues on the network into a SQLite database.

There are 2 tables:

______________________________________________________________________

"Issues" with a "ap_mac_adress" field

and

"AP_names" with a "ap_mac_adress" and a "ap_name" field.

______________________________________________________________________

I try to run a sql query that will count all unique ap_mac_adress in Issues.

This does the job:

SELECT DISTINCT ap_mac_address AS [Ap MAC Adresse], COUNT(ap_mac_address) AS Antall
FROM issues
GROUP BY ap_mac_address
ORDER BY Antall DESC

Result:
AP MAC Adresse Antall
xx:xx:xx:xx:xx:b2 1254
xx:xx:xx:xx:xx:f17 1021
xx:xx:xx:xx:xx:g4 723


But - I would like to have the name of the ap points, not their adress.

And when I do this, i get an error:

SELECT AP_Names, DISTINCT ap_mac_adress AS [Ap MAC Adresse], COUNT(ap_mac_adress) AS Antall
FROM issues
INNER JOIN aksesspunkter ON aksesspunkter.ap_mac_address = issues.ap_mac_adress
GROUP BY ap_mac_adress
ORDER BY Antall DESC

I think it has to do with the AP_Names in the select part, and GROUP by ap_mac_adress.

Can someone please guide me in the right direction??

Options: ReplyQuote


Subject
Views
Written By
Posted
SELECT, COUNT and Inner Join
1063
November 16, 2019 06:21AM
1013
November 16, 2019 11:57AM
249
November 16, 2019 04:30PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.