CODEWITHSUNDEEP
sqlsql-serverpivotpivot-tablebigint
Ahsan Ayub
Ahsan Ayub

Reputation: 11

"Error converting data type varchar to bigint" when creating pivot table

I'm (very) new to SQL and working on a mini-project as practice. I want to create a pivot table summarizing all the findings nicely but having some trouble and get an error:

Error converting data type varchar to bigint

I have a duration in seconds but want to format it as XX hours, XX minutes, XX seconds in the table. The chunk of code giving me the headache is:

SELECT 'Total hours ridden', [Member], [Casual]
FROM
    (SELECT 
         member_casual, 
         --ride_id,
         --DATENAME(MONTH, started_at) AS [Month],
         --rideable_type,
         --day_of_week,
         ride_length_in_sec = CONCAT(
            SUM(CAST(ride_length_in_sec AS BIGINT))/3600, ' ', 'hours', ' ',
            (SUM(CAST(ride_length_in_sec AS BIGINT))%3600)/60, ' ', 'minutes', ' ',
            (SUM(CAST(ride_length_in_sec AS BIGINT))%3600)%60, ' ', 'seconds')
     FROM
         AllCombined
     GROUP BY 
         member_casual) AS Src2
PIVOT
    (MAX(ride_length_in_sec)    
        FOR member_casual 
            IN ([Member], [Casual])
    ) AS Pvt2

I'm pretty sure it's the CONCAT bit that's causing the trouble because I've successfully used that chunk of code in a different query in the SELECT clause.

Any and all help would be greatly appreciated.

So far, I have tried changing the datatype of the column to BIGINT in hopes that getting rid of CAST might work, but just throws the same error. I tried using the other convention (using the + signs) to concatenate, but nothing.

Upvotes: -1

Views: 100

Answers (2)

Helder Reis
Helder Reis

Reputation: 1

Try something like this:

SELECT 
'Total hours ridden' AS [Total Hours Ridden], 
[Member], 
[Casual] FROM
(SELECT 
     member_casual, 
     total_seconds = SUM(CAST(ride_length_in_sec AS BIGINT))
 FROM
     AllCombined
 GROUP BY 
     member_casual) AS Src2 PIVOT
(MAX(total_seconds)    
    FOR member_casual 
        IN ([Member], [Casual])
) AS Pvt2 CROSS APPLY
(SELECT 
     [Member] = CONCAT([Member]/3600, ' hours ', ([Member]%3600)/60, ' minutes ', ([Member]%3600)%60, ' seconds'),
     [Casual] = CONCAT([Casual]/3600, ' hours ', ([Casual]%3600)/60, ' minutes ', ([Casual]%3600)%60, ' seconds')
) AS Formatted;

Upvotes: 0

user25043454
user25043454

Reputation:

I have tried changing the datatype of the column to BIGINT

How did you do this? It sounds like at least one value cannot be converted to bigint. e.g., someone stored 'foo' in ride_length_in_sec.

So you have two things to do:

  1. Identify the row(s) causing the problem:

    SELECT * FROM AllCombined
  WHERE TRY_CONVERT(bigint, COALESCE(ride_length_in_sec,0)) IS NULL;

  2. Decide what you want to do about those rows:

    • Correct them (if the source data still exists)
    • Delete the values (set to NULL or 0 or some arbitrary value)
    • Delete the rows
    • Leave them as is

If you correct them in any of the first three ways, you can then fix the table so that you don't store number of seconds as a string again (or maybe don't store number of seconds at all; StartTime and EndTime as datetime/datetime2 contain way more data, and are easy to derive other data from, like duration).

If you leave them there, you need to account for them in the query. A simple change to TRY_CAST will work:

    ride_length_in_sec = CONCAT
        (SUM(TRY_CAST(ride_length...,

But if you are going to perform counts or averages, and you're not going to fix the table, you need to decide what behavior you want when someone jams garbage in there again. Disregard the ride? Count it as 0 seconds?

Upvotes: 0

Related Questions