agora inbox for pgsql-sql@postgresql.orghelp / color / mirror / Atom feed
Help with complicated query (total SQL newb!) 2+ messages / 2 participants [nested] [flat]
* Help with complicated query (total SQL newb!) @ 2015-12-16 22:06 britt_mcclafferty <brittany.mcclafferty@gmail.com> 2015-12-17 00:19 ` Re: Help with complicated query (total SQL newb!) Adrian Klaver <adrian.klaver@aklaver.com> 0 siblings, 1 reply; 2+ messages in thread From: britt_mcclafferty @ 2015-12-16 22:06 UTC (permalink / raw) To: pgsql-sql 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Help with complicated query (total SQL newb!) 2015-12-16 22:06 Help with complicated query (total SQL newb!) britt_mcclafferty <brittany.mcclafferty@gmail.com> @ 2015-12-17 00:19 ` Adrian Klaver <adrian.klaver@aklaver.com> 0 siblings, 0 replies; 2+ messages in thread From: Adrian Klaver @ 2015-12-17 00:19 UTC (permalink / raw) To: britt_mcclafferty <brittany.mcclafferty@gmail.com>; pgsql-sql 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2015-12-17 00:19 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2015-12-16 22:06 Help with complicated query (total SQL newb!) britt_mcclafferty <brittany.mcclafferty@gmail.com> 2015-12-17 00:19 ` Adrian Klaver <adrian.klaver@aklaver.com>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox