How to write SQL query for grouping?

Question

SQL

The console swears at the design.

SELECT trips_amount, COUNT(company_name) AS cnt FROM cabs, trips GROUP BY trips_amount;

Error in the console - ERROR: aggregate functions are not allowed in GROUP BY

I also tried to do the construction like this -
SELECT COUNT(company_name) AS trips_amount FROM cabs, trips GROUP BY start_ts, end_ts;

there generally the console does not react

In the conditions:
Display the company_name field. Name the field with the number of trips trips_amount and display it.
Sort the results in the trips_amount field in descending order.

Answer 1

Well, first you need to correctly JOIN the cabs and trips tables. For a better understanding, I advise you to look at what returns:
SELECT * FROM cabs, trips;
I won’t tell you how to fix this - these are elementary basics.
Further, you do not need to write COUNT for anything, you need to clearly understand what the request should do. In this case, if grouping is not used, the query should extract all rows from the database according to the principle:
company1
... trip1 company1 ... trip2 company2 ... trip1
...
etc.
It is necessary to group by company, and aggregate trips with a function of their number, for example so:
SELECT company_name, COUNT(*) AS trips_amount ... GROUP BY company_name;
In SQL, when using aggregate functions, all non-aggregate columns must be placed in GROUP BY. In some cases, you can get away with dummy aggregation. For example, if we want to display its address along with the company, which will be the same for all lines anyway, we can do this:
SELECT company_name, MIN(company_address), ... GROUP BY company_name;
Although it makes more sense this way (and at the same time differences will emerge in companies with one common name such as LLC "Vector" and different addresses):
SELECT company_name, company_address, ... GROUP BY company_name, company_address;
And in the end it all needs to be sorted. Some databases like MySQL will allow you to do ORDER BY trips_amount DESC, but in Oracle, for example, you cannot use aliases here, you have to do ORDER BY COUNT (*) DESC.

Answer 2

Show table fields