Tag Info

Hot answers tagged

7

If the list of payment types and currencies are known, you can do this with CTEs. with paymenttypes as ( select 0 as ptype, Union select 1 as ptype, Union select 2 as ptype ), currencies as ( select 'Aaa' as currency union select 'Bbb' as currency ), sourcedata as ( select currency, ptype as paymenttype, 0 as amount from currencies, ...


3

One way to do this (check the SQLfiddle): select p1.id as id1, p2.id as id2 from people p1 join people p2 on p1.first_name = p2.first_name and p1.last_name = p2.last_name and p1.id < p2.id where not exists ( select 1 from ( select * from attributes a1 where a1.person_id = p1.id union all ...


3

Use a VALUES expression to generate an ad-hoc table and join to the other tables to get the respective IDs. INSERT INTO groupmember (person_id, group_id) SELECT p.person_id, g.group_id FROM ( VALUES ('alice'::varchar, 'girls'::varchar) , ('bob','boys', , ('alice','coolkids') , ('bob','coolkids') ) x (username, group_name) JOIN person p ...


2

Let's start by considering the downpayments and payments together. WITH AllPayments AS ( SELECT * FROM TableB UNION ALL SELECT * FROM TableC ) Now get the total and latest date. , YearlyDetails AS ( SELECT TableAID, YEAR([Date]) AS TheYear, SUM(Amount), MAX([Date]) AS LastDate FROM AllPayments GROUP BY TableAID, ...


2

No way! Every 'end' is not optimizable because of the leading wildcard. That means that if there are, say, a mere 3K 'end' entries in match, then there will be over a billion (350K * 3K) tests to perform! The query can be partially optimized by INDEX(type, string) -- in `match` INDEX(name) -- in `user` SELECT ... FROM user JOIN match ON ...


2

Build a table with n=1..5 and the comments (positive, etc); lets call it labels Get the counts (5 or fewer) Put together: SELECT labels.n, labels.comment, IFNULL(x.ct, 0) AS "count" FROM labels LEFT JOIN ( your big query to get the counts ) x ON x.n = labels.n ORDER BY labels.n The principle is "FROM (stuff you want all of) LEFT ...


2

I assumed you were using a pl/pgsql block (probably a function). For the generic case, you can use record, it can basically take a row of any structure, often used like DECLARE i record; BEGIN FOR i IN SELECT this, that, something, else FROM some_table LOOP ... END LOOP; There is also a possibility of defining a view: CREATE VIEW ...


2

Option 1 will always force a O(N) operation (a table/index scan) which disqualifies this option for significant amounts of data. You must use option 2 if you want a solution that scales with the amount of data. If there are very few rows (such as 3 or so) option 1 might actually be faster. I doubt that we are talking about that case here.


2

Definitely you want to use option 2. Not only will your queries be faster (= is always faster than like) but you can also index on that keyword field for even faster queries AND your storage space will be significantly reduced since you are not storing the long keyword strings for each website.


1

Thanks for your help. I changed my query like this and it's working finde. 😊 SELECT DATES.DAT, RET.RETURN_AMOUNT, ORD.ORDER_AMOUNT FROM ( SELECT TO_DATE('02.05.2014','dd.mm.yyyy')+ROWNUM-1 AS DAT FROM all_objects WHERE ROWNUM <= ...


1

You could join to the 2 tables separately (and GROUP BY) and then join them together: WITH dates (dat) AS ( SELECT TO_DATE('02.05.2014','dd.mm.yyyy') + ROWNUM - 1 AS dat FROM all_objects WHERE ROWNUM <= TO_DATE('02.05.2014','dd.mm.yyyy') - TO_DATE('02.05.2014','dd.mm.yyyy') + 1 ) , ret ...


1

I would do this: SELECT d.currency, d.paymenttype, SUM(CASE WHEN d.paymenttype = 0 THEN d.amount ELSE 0 END) AS [Type0], SUM(CASE WHEN d.paymenttype = 1 THEN d.amount ELSE 0 END) AS [Type1], SUM(CASE WHEN d.paymenttype = 2 THEN d.amount ELSE 0 END) AS [Type2] FROM dbo.data AS d GROUP BY d.currency, d.paymenttype


1

Without knowing what data you are starting with in each table, what is being produced and what is wrong/what you expected - I can only make some general observations to help you: 1)Code Formatting - Format your code better, use table aliases to shorten your code or at least tidy it up a bit. You will find it easier to read and therefore debug. 2)At a ...


1

Is it common to spread data of a single user operation across multiple rows? Think of the "entities": User, Review, Rating. A "user" may go to the restaurant many times, and give a "review" each time. The "review" may give ratings for multiple things, such as 'food' and 'location'. These are "Many-to-one" relationships, so Ratings has a review_id ...


1

I would try breaking this down into three subqueries and then join the results using full outer joins. select distinct coalesce(a.name, b.name, c.name) from ( select user.name from [user] inner join match on user.name = match.string where match.type = 'exact' ) a full outer join ( ...



Only top voted, non community-wiki answers of a minimum length are eligible