I’m enrolling in a Coursera course called SQL FOR DATA SCIENCE, offered by the University of California, Davis. I’m doing the last test and there is the next question based on a table of customer …
Tag: pivot
Show all varchar values with PIVOT
I need to show all varchar values with pivot but I’m not sure if that is possible? I have OCCUPATIONS table: Pivot query: Query result: Above query gives only 1 record in each column but I want to get all. Answer You ‘ll need an extra column, e.g. with ROW_NUMBER():
SQL Pivot count incomplete and complete
First of let me start by saying that my SQL know how is pretty limited. So I have below table Now I would like to create a pivot in SQL that looks like so Now PN from second column can repeat from …
SQL query using Sum() and count() functions
I’m trying to query in PostgresQL using the SUM function to get the total of 3 different row types (Root, Dynamic, Test). I used the Sum() function for the first attempt and the Count() function for the second attempt; both didn’t work sadly. I expect a syntax error (since I’m a beginner at …
Dynamic Pivot Table by Month
I’m trying to create a dynamic pivot table in SQL that will report based on month and year. I did a bunch of research and was able to come up with the below query: I am able to print the @column variable successfully, but the problems happen when I try to set it in the @dynamic variable. The error messa…
SQL Group by Transpose
I have the following Table charges Using the following query gets my the counts by Charge and Date Is there a way to transpose this to get the following? Thank you. Answer Use conditional aggregation:
SQL Aggregate Statement to Include Null Values and Values Not in a List
The result I am looking for is a table with each city, the number of unique yes votes, and the total yes votes. If there are yes votes in the votes table that don’t belong to a city in the users table, then it needs to return “other city” with the same aggregate stats. The result should look…
How to PIVOT a select result as mentioned in the query
I have query as mentioned below which will return a table like I’m trying to PIVOT this result as below Answer You can try the below way –
Count occurrences in SQL
I have a table with the following data And I would like to have for each ID how many occurrences of each type there are Is there a way in SQL (something like a pivot table) Answer I would recommend conditional aggregation. This is a cross-database solution that is more flexible than vendor-specific solutions …
MySQL Running Total By Group, Time Interval
I would like to take a table of customer orders like this: And create a table calculating a cumulative running total by week, segmenting the weeks by 7 days starting at the earliest date (2020-03-01, 2020-03-08, etc.). Something like: Thanks for the help! Answer You can use aggregation and window functions (t…