I'm currently using Postgres 9.6.16.
I am currently using 3 different tables to store a hypothetical users details.
The first table, called contact, this contains:
ID, Preferred_Contact_Method
The second table, called orders, This contains:
ID, UserID, Contact_ID (the id of a row, in the contact table that relates to this order)
The Third Table, Called order_details
ID, Orders_ID (the id in the orders table that relates to this order details)
The tables contain other data as well, but for minimal reproduction, these are the columns that are relevant to this question.
I am trying to return some data so that i can generate a graph, in this hypothetical store, There's only three ways we can contact a user: Email, SMS, or Physical Mail.
The graph is supposed to be 3 numbers, how many mails, emails, and SMS we've sent to the user; since in this hypothetical store whenever you purchase something you get notified of the successful shipment, these methods are 1:1 to the order_details, so if there's 10 order_detail rows for the same user, then we sent 10 tracking numbers, and since there can be multiple order_details (each item has a different row in order_details) in an order, we can get the count by counting the total rows of order details belonging to a single user/contact, then attributing to what kind of contact method that user preferred at the time of making that order.
To represent this better: If a new user makes a new order, and orders 1 apple, 1 banana, and 1 orange. For the apple, the user set preferred tracking number delivery as SMS, for the banana, they set it to EMAIL, for the orange, they thought it would be funny to set the tracking number delivery via MAIL. Now, i want to generate a graph to this users preferred delivery method. So i'd like to query all those rows and obtain:
SMS, 1
EMAIL, 1
MAIL, 1
Here's a SQL Fiddle link with the schema and test data: http://sqlfiddle.com/#!17/eb8c0
the response with the above dataset should look like this:
method | count
SMS, 4
EMAIL, 4
MAIL, 4
Best Answer