PostgreSQL Query Help (Structure and Syntax)-CodePudding

Task: For each day and market in September 2020: (1)How many appointments were completed? (2)How many hours were spent onsite? (3)How many routes were used to complete those appointments? NOTE: All three questions need to be fulfilled in one query.

CREATE TABLE appointments (
  "id" INTEGER,
  "date" TIMESTAMP,
  "market" VARCHAR(20),
  "count_appointments" INTEGER,
  "total_onsite_hours" DECIMAL
);

INSERT INTO appointments
  ("id", "date", "market", "count_appointments", "total_onsite_hours")
VALUES

Current query code:

Select 
    COUNT(count_appointments)As Appointments, market, date  
        FROM appointments
    WHERE date>='2020-09-01' AND date <='2020-09-30' 
        GROUP by market,date 
        Order by market,date,appointments DESC

I managed to solve the first portion of the task, but I'm struggling with part (2) to write the query for hours spent onsite. In addition, the third section (3) 'routes' information is included in another table so may need a "union" of some sort?

For context, I am working out of PostgreSQL and have the two tables pulled up in one database.

Any help/insight would be great!

Thank you.

CodePudding user response：

There is an error in your result for Q1. Check out this page: https://www.w3schools.com/sql/sql_count_avg_sum.asp and pay attention to the difference between SUM and COUNT - this will help with questions 1-2.

For question 3 move down in the tutorial into JOINS section: https://www.w3schools.com/sql/sql_join.asp and, provided you understand the basics of the relational databases, it will give you what you need.

You might also benefit from understanding subqueries. The reasonably good write-up is available here: https://careerkarma.com/blog/sql-subquery