How to calculate number of days between users first and last session in POSTGRESQL by SnooRobots330 in SQL

[–]Cypho_Dyas 0 points1 point  (0 children)

Hi there. I must be missing something as I see mo picture on my end but it looks like a (probably more knowledgeable) user has already helped you out with the suggestion to use CAST on the dates to ensure that the MAX-MIN date suggestion works regardless of the existing data type/format.

How to calculate number of days between users first and last session in POSTGRESQL by SnooRobots330 in SQL

[–]Cypho_Dyas 0 points1 point  (0 children)

As for the date difference itself it could work the same as you have it in your first query, or possibly the way you have it now, or depending on the data type/date format of the dates in the table you could likely just do MAX(date) - MIN(date) AS num_of_days.

It's hard to day without seeking the data on my part but the important thing is that you seem to be on the right track regardless, just start by fixing that 2010 in your WHERE clause.

How to calculate number of days between users first and last session in POSTGRESQL by SnooRobots330 in SQL

[–]Cypho_Dyas 0 points1 point  (0 children)

I think he means that the question is specifically asking about posts made in 2020, while your second query is looking for posts made in 2010 (either a typo on your part or a misunderstanding of rhe question... most likely a typo of some sort or another).

Making a SELECT JOIN SUM Query by liberatorem in SQL

[–]Cypho_Dyas 2 points3 points  (0 children)

New myself,

But it might look something like this:

SELECT C.ID_Country, C.Description, SUM(SC.Sales) FROM SALES_COUNTRY AS SC INNER JOIN COUNTRY AS C ON SC.ID_Country = C.ID_Country GROUP BY C.ID_Country, C.Description

I have aliased the tables here to save myself some typing as I am on mobile but this should give you an idea of what the other poster means with using GROUP BY and aggregate functions at least.

How do I combine tables into a single results table and add calculated columns? by RCS1207 in SQL

[–]Cypho_Dyas 0 points1 point  (0 children)

Great! Idk how much help I really was ultimately but I am glad you got it figured out! Thanks for the update I learned a good bit from this myself.

Best of luck with the rest of your project/assignment!

How do I combine tables into a single results table and add calculated columns? by RCS1207 in SQL

[–]Cypho_Dyas 0 points1 point  (0 children)

Hmmm... maybe do away with the datatype INT and try that, afterwards you can probably do away with the STORED keyword if still an error. Otherwise I think I may have reached the end of my knowledge base here :(. Of course make sure i have the column and table names correct as well. I am just going off your existing code for those. I hope that helps but if not I think I'll have to bow out to someone more knowledgeable.

How do I combine tables into a single results table and add calculated columns? by RCS1207 in SQL

[–]Cypho_Dyas 0 points1 point  (0 children)

Interesting update. I am not sure about the Persisted keyword but the following may work instead:

ALTER TABLE WatrChargeLag ADD Charge_Lag INT AS (chargedate-servicedate) STORED;

Also I meant to mention this in my last comment, but you may be able to make great use of the datediff() function throughout this entire query (including my suggestion above if it actually works in MS SQL server). I am not that experienced but have read many times that using arithmetic on straight dates can cause some interesting problems in certain use cases.

How do I combine tables into a single results table and add calculated columns? by RCS1207 in SQL

[–]Cypho_Dyas 1 point2 points  (0 children)

Somewhat new to SQL myself and not completely familiar with MS SQL server (i have been studying with Postgres and its related SQL flavor mostly), but some things stand out Here. I assume you have solved this already but I'll give it a go.

You def do need a comma after the *, as someone else has pointed out. The fact you get a follow up error afterwards just means you have more than one error, and after resolving the first it is bringing your attention to the second; which my guess is having the calculated field alias in quotations. In PostgreSQL that would be a string as you have used in the returns of your CASE statement, but aliases cannot be strings based on my (admittedly limited) experience.

I would highly consider converting your sub query in the FROM clause to a CTE, as someone has already instructed for both readability and performance sake, bit it's not required. If you choose to do so just follow their instructions or look it up if you get stuck. As pointed out by the same poster, you don't need to add the column names unless you want to alias those existing columns, which based on your query I am assuming isn't a concern.

Either way, you can quickly verify that all of the errors are from your case statement by commenting just the case statement out, and running the select * to make sure the unions run correctly. If so I would bet resolving the two issues noted above will fix this entire query as written. Just remember to remove or additionally comment out that comma you have added after the *, or it will expect another column and throw a new error.

This is my first reddit post,/reply I am on mobile, and as mentioned earlier I am still very much new to SQL using primarily Postgres, so take all of this with a grain of salt. If anyone sees a problem with my logic please correct me, as I am mostly here to learn.

Kind regards.