How to get count of an object, through 3 different tables in postgres with ID’s stored in each table

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 

Do the Fire Elemental’s Fire Form trait’s and Touch attack’s ongoing fire damage stack with each other?

Inspired by the following:

  • Does the Fire Elementa's Touch attack's ongoing damage stack with itself?

I realized that my own answer to that question hinges on the idea of features having the same name, but the Fire Elemental actually has two different features that cause extremely similar effects.

Notably it has the Fire Form trait and the Touch attack which state:

[…] In addition, the elemental can enter a hostile creature’s space and stop there. The first time it enters a creature’s space on a turn, that creature takes 5 (1d10) fire damage and catches fire; until someone takes an action to douse the fire, the creature takes 5 (1d10) fire damage at the start of each of its turns.

[…] If the target is a creature or a flammable object, it ignites. Until a creature takes an action to douse the fire, the target takes 5 (1d10) fire damage at the start of each of its turns.

Do these two ongoing effects stack with each other?

Automatically set x=0, after each input/output ? ($AfterEachInputOutput)

Is it possible to do something that I want, after each input/output automatically and silently?
I am looking for a feature like $ AfterEachInputOutput in the following code :

Example : to set variable x to 0 after each output

In[1] $  AfterEachInputOutput=(x=0;) ---> x=0 automatically and silently (not important because this is a starting step) In[2] x=2;x+2 Out[2] 4 ---> x=0 automatically and silently In[3] If[x=0,100,-100] Out[3] 100 ---> x=0 automatically and silently In[4] x=1;y=1; ---> x=0 automatically and silently In[5] x+10 Out[5] 10 ---> x=0 automatically and silently 

I have tried

$  Post=(x=0;) 

or

$  Preprint=(x=0;) 

But it didn’t work well.

Is there a cross-platform way to compare data in one columnd on each sitde of replicated data, like a checksum or hash?

I have an Oracle 12 database with lots of tables, and I am replicating several of the tables (a subset of rows) into a SQL Server 2016 database. The subset of rows can be established with a WHERE clause on the Oracle side.

I have two web services that can expose anything I want to from that data, one on each side.

Do you have a suggestion for an approach of what I can expose, then compare to find out if the data between the systems matches?

I am currently exposing from one table, which has a few million rows the COUNT(*), which is a no-brainer since it is very efficient. So far, so good.

I’m also exposing the SUM of each of a few NUMBER(18,2) columns and comparing it to the corresponding SUM on the SQL Server side. However, this is problematic, as it has to scan the whole table in SQL Server; it is sometimes blocked, and sometimes might cause other processes to block. I imagine similar problems could occur on the Oracle side too.

Also, the SUM will not tell me if the rows match–it will only tell me that the totals match; if an amount was improperly added to one row and subtracted from another, I wouldn’t catch it.

I’ve pondered whether Oracle’s STANDARD_HASH might help me, but it seems cumbersome or error-prone to try to generate the exact same HASH on the SQL Server side, and also this doesn’t help with the inefficiency/blocking nature of the call.

So is there any way to have both databases keep track of a hash, checksum, CRC code, or other summary of a column’s data, that is efficient to retrieve, and that I can then use to compare to have some idea whether data is the same on both sides? It need not be a perfect solution–for example, comparing SUMs is close but perhaps not quite good enough.

As a first stab, I created an "summary" indexed view, with columns derived from SUMs, on the SQL Server side. This makes querying the view very fast, but incurs additional penalty on every write to the large table underneath. Still, I think it will work, but I’d like to improve on it. Other, better ideas?

When do Druids gain the ability to cast each spell level?

I understand spells and that spell levels don’t correspond with character level but on this website it says:

Spell level and character level don’t correspond directly. Typically, a character has to be at least 17th level, not 9th level, to cast a 9th–level spell.

I understand this, but what character level is a Druid required to be to cast a spell at any given spell level?

Just to be clear, I am not asking about spell slots here.

What does this the DH 2e’s Mountain Warrior’s ‘each advance’ mean?


Mounted Warrior

Requirements: Rank 2 (Trained) in any Operate skill or Rank> 2(Trained) in Survival skill, BS 30 or WS 30

Apptitude 1 Weapon Skill / Ballistic Skill

Aptitude 2 Offence

Reduces any penalty for making attacks (Melee or Ranged) from a moving vehicle or mount by 10 for each advance.

I’ve not been able to other sources use similar wordings. Does this mean each rank in all operate and survivor skills gives a +10? Is it just the highest ranked of the 4 skills? Or is it something else?

A flag (indicator) that shows for each row which conditions was used to select it

Suppose I have the table foo. I want to filter the rows of this table using different conditions (cond_1, cond_2, …, cond_nthe order is important) combined in a disjunction, i.e.:

SELECT * FROM foo WHERE cond_1 OR cond_2 OR ... OR cond_n; 
  1. Is it possible (in PostgreSQL 13) to distinguish for each row which condition cond_i was used to select it? Since I stated that the order is important the question may be reformulated as – I want to know the first cond_i that selected the row, for each row.

Now consider that I have in foo a column for this purpose, e.g. selected_by.

  1. Is it possible to distinguish for each row which condition cond_i was used to select it and store it in selected_by? (the same as 1st question but for an UPDATE statement)

Two things are important: the order of conditions (as it was highlighted earlier); performance (i.e. table foo may have many rows, the number of conditions is small, up to 5-10).

If a spell is twinned, does the caster need to provide costly material components for each target?

The Divine Soul Sorcerer can gain access to the revivify spell pretty easily (along with other costly resurrection spells), which has a costly material component that the spell consumes (300 gp worth of diamonds).

If after a particularly rough battle, can the Sorcerer use revivify on two separate creatures who have died for the cost of just 300 gp worth of diamonds or would it require 600 gp worth?

For the sake of this question, assume that a dead creature is a creature and not an object. If it helps, you could consider the stoneskin spell as well.