Need help with creating an sql to get information from two tables

1

Hello, it's been years since I've dazzled in sql.I get an issue when trying to create my sub query. Something to do with my group by. Anyway I've gotten nowhere after 2 days. Hope someone can help.

So I have two tables.

access_points

Fieldname: id
Fieldname: name
Fieldname: mac_address
Fieldname: product
Fieldname: tower_id
Fieldname: type
Fieldname: created_at
Fieldname: updated_at
Fieldname: ip_address
Fieldname: tag
Fieldname: isActive

access_point_statistics

Fieldname: id
Fieldname: access_point_id
Fieldname: mode
Fieldname: dl_retransmit
Fieldname: dl_retransmit_pcts
Fieldname: dl_pkts
Fieldname: ul_pkts
Fieldname: dl_throughput
Fieldname: ul_throughput
Fieldname: status
Fieldname: connected_sms
Fieldname: reboot
Fieldname: dl_capacity_throughput
Fieldname: created_at
Fieldname: updated_at
Fieldname: lan_speed_status

OK I would like the access_point_id, the MAX (dl_throughput) and the AVG(connected_sms) from table 2 where created_at is between $start_date and $end_date. I also require the name from table 1 and to join both tables would be the id from table 1 with the access_point_id from table 2.

This is what i have so far.. but I get GROUP BY error.

select
ap.name,
a.access_point_id,
a.max,
a.avg
from
access_points ap
inner join (
select
aps.access_point_id,
max(aps.dl_throughput) as max,
avg(aps.connected_sms) as avg
from
access_point_statistics aps
where
aps.created_at BETWEEN '$start_time%' and '$end_time%'
group by
1) a
on ap.id = a.access_point_id

2

This doesn't appear to be SQL Server code, so could you let us know what database engine you're using?

Whichever system it is, the likely answer is going to be that you need to use GROUP BY aps.access_point_id in your GROUP BY rather than GROUP BY 1.

3

Hi rnovelo79,

You could try something like below;

SELECT ap.access_point_id, aps.name, MAX(ap.dl_throuput) AS [Max_d/l_throughput],
AVG(ap.connected_sms) AS [Avg connected_sms]
FROM access_points ap
JOIN access_point_statistics aps ON ap.id = aps.access_point_id
WHERE aps.created_at BETWEEN
—//START_DATE\— AND
—//END_DATE\—
GROUP BY aps.access_point_id, ap.name;

Obvs change —//START_DATE\— & —//END_DATE\— to suit.
I’ve typed the code in by phone so layout may need adjusting but should do the trick.