Thread: Help with complicated query (total SQL newb!)

Hi there,

I am only just learning SQL and have three specific questions on correct
syntax for my queries... 

*1.* The 'Data1' and 'Data2' columns have stings which contain both numbers
and letters. In the query below I have removed the letters and re-named the
trimmed versions. I want to cast the remaining numbers to numerics and run
aggregate functions on them (ie AVG, MAX etc). However, whenever I wrap the
TRIM in a function I am getting errors. How do I fix this?

Here is my query

SELECT  data1,  TRIM(TRAILING ' total bookmarks' FROM data1) as Bookmarks_trim,  data2,  TRIM(TRAILING ' folders' FROM
data2)as Folders_trim,  event_code,  user_id
 
FROM events
WHERE event_code =8

And this is what it returns: http://screencast.com/t/DCvey2sAxZ

*2. *I want to add an additional parameter to the query above to show only
DISTINCT instances of the user_id. The separate query I have for that is
below. How do I combine the two?

SELECT DISTINCT
*
FROM (SELECT DISTINCT user_id, event_code, data1, data2        FROM events) AS temp
WHERE event_code = 8 

This returns: http://screencast.com/t/IXhpix0vLNSp

*3. *Lastly, I want to be able to sort by DESC on both the trimmed data1
column and data2 (to see the users with the highest number of folders and
bookmarks) I know you do this with ORDER BY but I am not sure where it would
go in such a large query.

ANY help would be hugely appreciated. 



--
View this message in context: http://postgresql.nabble.com/Help-with-complicated-query-total-SQL-newb-tp5877942.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

On 12/16/2015 02:06 PM, britt_mcclafferty wrote:
> Hi there,
>
> I am only just learning SQL and have three specific questions on correct
> syntax for my queries...
>
> *1.* The 'Data1' and 'Data2' columns have stings which contain both numbers
> and letters. In the query below I have removed the letters and re-named the
> trimmed versions. I want to cast the remaining numbers to numerics and run
> aggregate functions on them (ie AVG, MAX etc). However, whenever I wrap the
> TRIM in a function I am getting errors. How do I fix this?

What error are you getting?

When I do this:

test=> select max(TRIM(TRAILING ' total bookmarks' FROM '34 total 
bookmarks')); max
----- 34

it works.

Do you have empty strings in your Data1 and Data2 columns?


>
> Here is my query
>
> SELECT
>     data1,
>     TRIM(TRAILING ' total bookmarks' FROM data1) as Bookmarks_trim,
>     data2,
>     TRIM(TRAILING ' folders' FROM data2) as Folders_trim,
>     event_code,
>     user_id
> FROM events
> WHERE event_code =8
>
> And this is what it returns: http://screencast.com/t/DCvey2sAxZ

FYI, it is generally better to just cut and paste your results directly 
into the post.

>
> *2. *I want to add an additional parameter to the query above to show only
> DISTINCT instances of the user_id. The separate query I have for that is
> below. How do I combine the two?

Have you looked at DISTINCT ON:

http://www.postgresql.org/docs/9.4/interactive/sql-select.html#SQL-DISTINCT

>
> SELECT DISTINCT
> *
> FROM (SELECT DISTINCT user_id, event_code, data1, data2
>             FROM events) AS temp
> WHERE event_code = 8
>
> This returns: http://screencast.com/t/IXhpix0vLNSp
>
> *3. *Lastly, I want to be able to sort by DESC on both the trimmed data1
> column and data2 (to see the users with the highest number of folders and
> bookmarks) I know you do this with ORDER BY but I am not sure where it would
> go in such a large query.

That depends on what you are looking at, the overall aggregated totals 
for a user or the totals by event or some other parameter. Probably need 
to show the actual query.

>
> ANY help would be hugely appreciated.
>
>
>
> --
> View this message in context: http://postgresql.nabble.com/Help-with-complicated-query-total-SQL-newb-tp5877942.html
> Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
>
>


-- 
Adrian Klaver
adrian.klaver@aklaver.com