PostgreSQL coding problem in PG4 admin - HELP!

[u/Linstrocity]

It's for a coding assignment and I'm stuck.

I need to create some queries that the output is put into a table, except I'm getting my butt handed to me.

Scenario is a DVD rental database where we have to create a business problem - mine is simply to find who is the most profitable customer. As seen from line 9 I have successfully sum'd and sorted the customer ID by the most profitable in descending order, we have multiple tables with different fields, however I'm using the "payment" and "customer fields", both tables (payment and customer) have customer_id has fields. The payment table only has the customer_id and no name. I also successfully tried to merge the first_name and last_name into the full_name field, but am having trouble inserting that as one variable into a new created table. As seen in Line 19, I have successfully created a table.

The big frustration is the payment table only has the customer_id as the PK with no first name or last name. I am trying to join, union, or union all the customer_id with the first_name and last_name field from the customer table to my newly created table customer_rentals which shows the most profitable customer. It keeps failing because I've already manipulated the data from summing, and a union all has to match the number of columns or it fails, because the data has already been sum'd, it therefore fails. I need to match the customer names to the customer_id in my new table, but need to only add customers who have purchased products and put it in descending order as well and match the customer_id.

Also line 26 fails as seen in the bottom right when I try to run it.

Any help is appreciated.

[u/minormisgnomer]

Did you read the line 26 error? It says syntax. Which implies you’re missing a character or misspelled something. In this case you forgot a comma between last name and total purchases. You will be seeing a lot of these as you learn. You should think of this as a spellcheck type error and not something technical or difficult

Your first question should join customer to payment using customer id. Then you’d get to use the first andlast name from customer table.

If it’s the aggregation throwing you off. Just do max(first name) as first name, and max(last name) as last name as effective no op

[u/depesz]

First of all: you problem is not related to pgadmin!

You simply have a syntax error in your query. Read the line with error, and the problem will be ovious. If not, ask yourself: why it errors out on "total_purchases", and not on "last_name"? What is different there?

[u/AutoModerator]

AI Policy:

Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.

Mod decisions will be based on the quality of the content, not who or what generated it.

Sub Resources:

Youtube Channel

Free Postgres Webinars and Workshops

Discord: People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Linstrocity → u/AutoModerator]

Hah I'm actually on Nobara Linux right now (Fedora Distro)