How to "transpose" date data into columns in SQL?
I have a table with
(id, name, creation_timestamp)
I want to write a query that gives:
name, 2019-09-15, 2019-09-16, ... , 2019-10-15
Columns date are for the last 30 days.
In each column there will be the count of records per name that matches the date.
I know how to create this as rows
select name ,cast ( creation_timestamp as DATE) ,count(1) from my_bale group by name, cast ( creation_timestamp as DATE)
But this gives me 3 columns and the dates are listed in the 2nd columns. How can I convert this to the required output?
Lets say that my table has
id, name , creation_timestamp 1 A 2019-10-10 21:52:22.904 2 A 2019-10-10 22:52:22.904 3 B 2019-10-10 21:52:22.904 3 C 2019-10-11 21:52:22.904
Output should be:
name, 2019-09-15, 2019-09-16, ... , 2019-10-10, 2019-10-11, ..., 2019-10-15 A 0 0 2 0 0 B 0 0 1 0 0 C 0 0 0 1 0
Similar to the question sql date range in column and count by group but it didn't get any answer
I'm using Presto but a native SQL solution is great
See also questions close to this topic
Sql Limitation on Database Name
I was trying to make a sql database and i have faced some issues regarding INSERT INTO TABLE. I have noticed that sql is not allowing me to insert data in a table if the name of Database is u759286173 i am able to insert data into table using any other name but this particular (u759286173) database name is not allowing me to insert data in table
I have tried various database names which works 1) 759286173 2) 759286173_quebec 3) 759286173_grvnaz 4) u_grvnaz
Can someone please explain me that why data is not inserting in table when i allow the database name to be u759286173
My Database name : u759286173 My Table name : mydb
Please reply if u face the same issue or have a solution. Any help is appreciated.
group by issue in sql
i'm trying to get in a new column the sessions who are between 08:00 and 18:00. You can see my last CASE in the CTE. For each date there should be a new column "TotalRestrictedSessions" which indicate how many session were on that particular date. If there are none, in this case i have to write 0. I suspect that my problem is when i convert the DATE?
WITH ParkeonCTE AS ( SELECT OccDate = CONVERT(DATE, OC.LocalStartTime), TotalOccSessions = COUNT(OC.SessionId), AuthorityId, TotalOccDuration = ISNULL(SUM(OC.DurationMinutes),0), TotalNumberOfOverstay = SUM(CAST(OC.IsOverstay AS INT)), TotalMinOfOverstays = ISNULL(SUM(OC.OverStayDurationMinutes),0), (CASE WHEN OC.OspId IS NULL THEN 'OffStreet' ELSE 'OnStreet' END ) AS ParkingContextType, (CASE WHEN CAST(OC.LocalStartTime AS TIME) >= '08:00:00' AND CAST(OC.LocalStartTime AS TIME) <= '18:00:00' THEN COUNT(OC.SessionId) END ) AS TotalRestrictedSessions FROM Analytics.OccupancySessions AS OC WHERE OC.AuthorityId IS NOT NULL GROUP BY CONVERT(DATE,OC.LocalStartTime), OC.AuthorityId,OC.OspId ) SELECT OC.OccDate, OC.ParkingContextType, OC.AuthorityId, OC.TotalRestrictedSessions, SUM(OC.TotalOccSessions) AS TotalOccSessions, AVG(OC.TotalOccDuration) AS AvgOccMinutesDuration, -- wrong SUM(OC.TotalOccDuration) AS TotalOccDuration, SUM(OC.TotalNumberOfOverstay) AS TotalNumberOfOverstay, SUM(OC.TotalMinOfOverstays) AS TotalMinOfOverstays, CAST(AVG(OC.TotalMinOfOverstays) AS decimal(10,2)) AS AvgMinOfOverstays -- wrong FROM ParkeonCTE AS OC GROUP BY OC.OccDate, OC.AuthorityId, OC.ParkingContextType ORDER BY OC.OccDate DESC
Create a voting system and avoid double voting
I am thinking about a project and I would like some clarification about a feature of the project. So my system based on voting, a user registered on the site can vote in a list that contains all members of my site, so people can vote between them, and suddenly I think how to do that a person can not repeat his vote more than once? I told myself thanks to his ip! But several things escape me, as for example if he uses a VPN? Or if he uses multiple devices? Is there really no way for the same person to vote multiple times?
Thank you for your feedback!
change the range of the pivot table source data
I want to update the range of the source data my pivot table is reading from to make it more dynamic, however I been getting tons of error code.
Private Sub CommandButton2_Click() Dim Data_sht As Worksheet Dim Pivot_sht As Worksheet Dim StartPoint As Range Dim DataRange As Range Dim PivotName As String Dim NewRange As String 'Set Variables Equal to Data Sheet and Pivot Sheet Set Data_sht = Workbooks("bdncasemacro.xlsm").Worksheets("Filter") Set Pivot_sht = Workbooks("bdncasemacro.xlsm").Worksheets("Pivot") 'Enter in Pivot Table Name PivotName = "PivotTable4" 'Dynamically Retrieve Range Address of Data Set StartPoint = Data_sht.Range("A1") Set DataRange = Data_sht.Range(StartPoint, StartPoint.SpecialCells(xlLastCell)) NewRange = Data_sht.Name & "!" & _ DataRange.Address(ReferenceStyle:=xlR1C1) 'Change Pivot Table Data Source Range Address Pivot_sht.PivotTables("PivotTable4").ChangePivotCache _ Workbooks("bdncasemacro.xlsm").PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=NewRange) 'Ensure Pivot Table is Refreshed Pivot_sht.PivotTables(PivotName).RefreshTable 'Complete Message MsgBox PivotName & "'s data source range has been successfully updated!" End Sub
one of the error being this:
Pointing towards the changing source address portion of the code, is there anything I need to add to my code or change? Thanks in advance!
How “pivot” row from two tables MySQL without usign aggregation
I´m trying to do this:
I´ve Medicion table:
idA measure date Var_idVar idLecture 1 -74.76284851 19/11/2019 1 1 2 6.165739368 19/11/2019 2 1 3 -74.76284851 19/11/2019 1 2 4 6.165739368 19/11/2019 2 2 5 -74.76284851 20/11/2019 1 3 6 6.165739368 20/11/2019 2 3 . . . . . . . . . . . . . . . 108 36 21/11/2019 3 6 109 5 22/11/2019 3 6 110 4 23/11/2019 3 5 111 21 24/11/2019 3 5 112 24 25/11/2019 3 5 113 15 26/11/2019 3 6
and Var table:
idVar nombre unit 1 xMagna m 2 yMagna m 3 Temperature °C
And I need to create a view with this result:
Fecha xMagna yMagna Lectura 19/11/2019 -74.76284851 6.165739368 1 19/11/2019 -74.76284851 6.165739368 2 20/11/2019 -74.76284851 6.165739368 4
As you can see I need to do two new columns named by xMagna and yMagna and group this by idLecture. Temperature is not necessary for me in this step, then I dont create this column.
I see this question, but is not a valid solution for me, because I cant use any aggregation function, because I need the value of each cell, not a count over all rows or a sum, etc
Anyone can help me?
Thanks in advance
I'm trying to do a dinamic pivot in mysql
I am trying to do a dinamic pivot in mysql but it is saying that a I have a sintax error, how could I correct this?: MySql 7
SET @sql = NULL; SELECT GROUP_CONCAT(DISTINCT CONCAT( 'SUM(CASE WHEN subject = "', subject,'" AND ', (CASE WHEN media IS NOT NULL THEN CONCAT("media = ",media) ELSE media IS NULL END), ' THEN 1 ELSE 0 end) AS ', subject, IFNULL(media,'') ) ) INTO @sql FROM cs_media_simonline; SET @sql = CONCAT('SELECT user_id, ', @sql, ' FROM cs_media_simonline GROUP BY user_id'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
this the error: '.0000000,SUM(CASE WHEN subject = "MATEMATICA" AND media = 0.0000000 THEN 1 ELSE' row 1
Athena geospatial SQL joins never complete
A very basic geospatial join, based on this example, times out every time.
polygonscontains 340K polygons, while
pointscontains 5K rows with latitude/longitude pairs (and an ID). Both are single .csv files in S3.
SELECT poly.geometry, p.id FROM polygons as poly CROSS JOIN points as p WHERE ST_CONTAINS (ST_POLYGON(poly.geometry), ST_POINT(p.lon, p.lat));
The SQL query above never completes in the default 30-minute Athena query time limit.
I've found vanilla Athena queries on large-ish data sets are fairly performant, but I'm not sure whether the geospatial functionality can handle (million x million) joins, let alone (billion x million) joins.
Thought I'd ask before moving back to EMR+Spark solution.
Why SQL query returns values that should be filtered out by condition in Presto?
I am not able to understand the output of my SQL queries and trying to get some sense of it. My data set is as follows
table name: my_table assignee -------- Mat_Bar dan.eml mon.gal
My first simple query
WITH rows as ( SELECT distinct(assignee) FROM my_table WHERE assignee NOT LIKE '%_%' ) SELECT * FROM rows WHERE assignee = 'Mat_Bar'
Above query returned zero records as expected
Now I am adding another OR condition to above query as follows
WITH rows as ( SELECT distinct(assignee) FROM my_table WHERE assignee NOT LIKE '%_%' OR assignee NOT LIKE '%.%' ) SELECT * FROM rows WHERE assignee = 'Mat_Bar'
Above query returns one row "Mat_Bar"
I am expecting my second query should return zero records too as the first one but it doesn't. Any suggestions?
PS: I am running the query on AWS Athena (Presto)
Using Presto with custom SQL like DSL
My web application shall obtain insights over parquet files stored on data lake with using custom SQL like DSL. Now I use Spark for transforming this DSL commands into DataFrame API but I realise that it's possible to achieve better performance with using MPP solutions like Presto\Impala etc. as they always have a prepared process for SQL execution. But I have a limitation - transforming DSL to SQL should be done some third party place. I'm aware that I can build a proxy app for transforming from this DSL to SQL. My question is the next: is there any way to make this transformation on Presto's side?