Tag Info

Hot answers tagged

3

Your LEFT JOIN is converted to a INNER join because the WHERE contains conditions about the columns of the right (linkcats) table. Then, since the conditions are contradicting, the query will always return 0 rows: WHERE linkcats.cat_id IN (17,1,35,33,50) AND linkcats.cat_id NOT IN (28) You need a semijoin and an anti-semijoin, which can be written with ...


3

I'm going to assume SQL Server here, but you can use some variation on this technique with most RDBMS I think. You need to build a table expression representing the data you want in columns, in this example with a CTE: ;WITH MDs as ( SELECT MDName = MAX(CASE WHEN Description = 'MDname' THEN Text END) ,Facility = MAX(CASE WHEN Description = ...


2

You can use this one: SELECT custID, InteractionDate, IFNULL(ROUND((yes)/(yes+NO), 2),0) AS Success, sales FROM (SELECT custID, InteractionDate, sum(if(Purchased=='T',1,0)) AS yes, sum(if(Purchased!='T',1,0)) AS NO, sum(sales) AS sales FROM (SELECT 1 AS custID, 20150312 AS ...


1

There is no connection between these two tables, so a join won't really get what you need. If everything goes into a single result set, you need to UNION them together. Note that when doing a union, you get one set of columns, and you can't mix datatypes within a column (no concern here since all your data are FLOATs). Here's an example starting point: ...


1

The question would have been a better candidate for stackoverflow. Anyways, the problem with your SQL is that the GROUP BY should only be on the author name and not on author name + book title. From the GROUP BY on author name, get the max(number_of_pages) and wrap it inside another SELECT to to get the books by that author that have that those number of ...


1

I'm giving it a shot, still not sure if I got it: select 100.0*(count(b.taba_id) + count(c.taba_id)) / (( select count(1) from b) + (select count(1) from c)) from taba as a left join tabb as b on a.id = b.id left join tabc as c on a.id = c.id Some thoughts, count will newer return null so coalesce is pointless. If the above is correct it ...


1

This would be very simple in SQL. As far as I can read the BigQuery Query reference, it supports all (GROUP BY clause, COUNT() and SUM() functions, CASE expressions): SELECT custID, InteractionDate, 1.0 * COUNT(CASE WHEN Purchased = 'T' THEN 1 END) / COUNT(*) AS Success, SUM(Sales) AS Sales FROM tableName GROUP BY custID, ...


1

There are many ways. Here is one (should be a fast variant to retrieve all rows): SELECT t1.*, t2.* FROM table1 t1 JOIN ( SELECT DISTINCT table_1_id, table_2_id FROM table1_2 ) t1_2 ON t1_2.table_1_id = t1.id JOIN table2 t2 ON t2.id = t1_2.table_2_id; Why not remove the duplicates permanently? And add a UNIQUE constraint ...


1

In Postgres 9.4 you can simplify with the aggregate FILTER clause: CREATE VIEW fumbbl.matches AS SELECT m.fmid, m.time, d.name AS division , min(s.coachbracket) FILTER (WHERE NOT s.away) AS hbracket , min(t.name) FILTER (WHERE NOT s.away) AS hteam , min(c.name) FILTER (WHERE NOT s.away) AS hcoach , min(s.score) ...


1

Another way for the same problem: SELECT a.Text AS Name, b.Text AS Facility, COUNT(*) AS Occurences FROM data AS a JOIN data AS b ON a.Document_ID = b.Document_ID WHERE a.Description = 'MDname' AND b.Description = 'Facility' GROUP BY a.Text, b.Text ; While for the provided data, both @JNK's ...



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