Tag Info

New answers tagged

0

Not possible directly in MySQL. Might be possible with a Stored Procedure (which can have loops). Recommend writing a recursive routine in PHP/Perl/Java/etc. INDEX(user, sponsor) (or PRIMARY KEY) would be very efficient for finding the 1 sponsor or the 1000 sponsors for a user -- at the first level. A second query, using an IN(...) from the first level ...


0

If I understand correctly, you could have any number of columns from table B? If so, then you will need to dynamically create the sql statement (by querying the sys.columns table) and just wrap each column from B in your isnull(field, ifnullvalue).


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: ...


0

The question is not very clear but my guess is that the values are updated with nulls because your IFs have null in there! Change the statement to: UPDATE charged AS u INNER Join mis_charged AS v ON v.id = u.id SET u.act_val = if(v.DAYS = u.Activation_date, USG, NULL), -- NULL -- if you want null when the condition fails ...


0

When using inner joins there is no semantic difference between the two, and it would usually be make sense to put join related conditions in the JOIN and filtering ones in the WHERE clause. However, when using outer joins the semantics are not the same. for example: in a left join - rows from the left table will be returned even if there the JOIN ON ...


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

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, ...


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 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 ...


0

The tables can be queried in a few different ways depending on what you want to do. If you wish to (Example 2) extract all the Reviews with a particular Category you can create a procedure like this: SELECT review_id, professional_rating, efficiency_rating, referral_rating... FROM Review r INNER JOIN Review_Category rc ON r.review_id = rc.review_id INNER ...


0

SELECT st.topup_nr, n.name AS network, p.name AS supplier , st.topup_sum, st.topup_qty FROM ( SELECT t.topup_nr, s.network_id, s.supplier_id , sum(t.amount) AS topup_sum , count(*) AS topup_qty FROM ( SELECT simcard_id, amount , row_number() OVER (PARTITION BY simcard_id, ORDER BY date) AS topup_nr FROM ...


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 ...


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 = ...


0

Here's what wound up working for me: select a.desc, count(union_all.TabA_id) as "Count of A", to_char(round(ratio_to_report(count(union_all.TabA_id)) over()*100)) || '%' as "Percentage of Total" from TabA a left join ( select TabA_id from TabC union all select TabA_id from TabB ) union_all on ...


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 ...


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 ...



Top 50 recent answers are included