Count

Edit 变更圈子

查看历史

Share

Flag

QuestionHow can I select count(*) from two different tables (call them tab1 and tab2) having as result:Count_1 Count_2 123 456I've tried this:select count(*) Count_1 from schema.tab1 union all select count(*) Count_2 from schema.tab2But all I have is:Count_1 123 456Answer1SELECT ( SELECT COUNT(*) FROM tab1 ) AS count1, ( SELECT COUNT(*) FROM tab2 ) AS count2 FROM dualCommentswhy do you need dual? what does that mean?It's a fake table with one record. You can't have SELECT without FROM in Oracle.Answer2As additional information, to accomplish same thing in SQL Server, you just need to remove the "FROM dual" part of the query.Answer3Just because it's slightly different:SELECT 'table_1' AS table_name, COUNT(*) FROM table_1 UNION SELECT 'table_2' AS table_name, COUNT(*) FROM table_2 UNION SELECT 'table_3' AS table_name, COUNT(*) FROM table_3It gives the answers transposed (one row per table instead of one column), otherwise I don't think it's much different. I think performance-wise they should be equivalent.Answer4My experience is with SQL Server, but could you do:select (select count(*) from table1) as count1, (select count(*) from table2) as count2In SQL Server I get the result you are after.Answer5Other slightly different methods:with t1_count as (select count(*) c1 from t1), t2_count as (select count(*) c2 from t2) select c1, c2 from t1_count, t2_count / select c1, c2 from (select count(*) c1 from t1) t1_count, (select count(*) c2 from t2) t2_count /The content is from StackOverflow which is translated and used in accordance with the CCBY-SA 4.0 license agreement. Original link: Select count(*) from multiple tables

QuestionHay, I'm using django 1.2 and i want to know how to count rows from a raw queryset(RawQuerySet).The traditional .count() method doesn't work.Heres my queryquery = "SELECT *, ((ACOS(SIN(%s * PI() / 180) * SIN(lat * PI() / 180) + COS(%s * PI() / 180) * COS(lat * PI() / 180) * COS((%s - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM app_car WHERE price BETWEEN %s AND %s HAVING distance<=%s ORDER BY distance ASC" cars = Car.objects.raw(query, [lat, lat, lon, min_price, max_price, miles]) return HttpResponse( cars )And its returningCar_Deferred_model_id_user_id objectAny ideas?Answer 1Use the 'len()' function. This would give:query = "SELECT *, ((ACOS(SIN(%s * PI() / 180) * SIN(lat * PI() / 180) + COS(%s * PI() / 180) * COS(lat * PI() / 180) * COS((%s - lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM app_car WHERE price BETWEEN %s AND %s HAVING distance<=%s ORDER BY distance ASC" cars = Car.objects.raw(query, [lat, lat, lon, min_price, max_price, miles]) return HttpResponse(len(list(cars))Aside: there's some useful information on the Django 1.2 Model.objects.raw() method at: http://djangoadvent.com/1.2/smoothing-curve/ [Looks like that site might have expired, but the Internet Archive has it at: http://web.archive.org/web/20110513122309/http://djangoadvent.com/1.2/smoothing-curve/ ]The content is from StackOverflow which is translated and used in accordance with the CCBY-SA 4.0 license agreement. Original link: Django count RawQuerySet

QuestionHow do I go about selecting COUNT(*)s from multiple tables in MySQL?Such as:SELECT COUNT(*) AS table1Count FROM table1 WHERE someCondition JOIN?? SELECT COUNT(*) AS table2Count FROM table2 WHERE someCondition CROSS JOIN? subqueries? SELECT COUNT(*) AS table3Count FROM table3 WHERE someConditionEdit:The goal is to return this:+-------------+-------------+-------------+ | table1Count | table2Count | table3Count | +-------------+-------------+-------------+ | 14 | 27 | 0 | +-------------+-------------+-------------+Answer 1You can do it by using subqueries, one subquery for each tableCount :SELECT (SELECT COUNT(*) FROM table1 WHERE someCondition) as table1Count, (SELECT COUNT(*) FROM table2 WHERE someCondition) as table2Count, (SELECT COUNT(*) FROM table3 WHERE someCondition) as table3CountAnswer 2You can do this with subqueries, e.g.:select (SELECT COUNT(*) FROM table1 WHERE someCondition) as table1Count, (SELECT COUNT(*) FROM table2 WHERE someCondition) as table2Count Answer 3You can use UNION SELECT COUNT(*) FROM table1 WHERE someCondition UNION SELECT COUNT(*) FROM table2 WHERE someCondition UNION SELECT COUNT(*) FROM table3 WHERE someConditionThe content is from StackOverflow which is translated and used in accordance with the CCBY-SA 4.0 license agreement. Original link: COUNT(*) from multiple tables in MySQL

QuestionI have seen how to do multiple counts for different tables, but never how to combine them all into one. I have a MySQL DB where I am running the following query:SELECT characters.name, COUNT(*) AS wiki_unlocks FROM wiki_items INNER JOIN characters ON characters.character_id=wiki_items.character_id GROUP BY wiki_items.character_id ORDER BY wiki_unlocks DESC LIMIT 10;This is giving me the following which is great:name wiki_unlocks player1 2 player2 1I want to get a combined count of all of the 'wiki_xxxx' tables. For example I want 'wiki_items'(above) + 'wiki_armors' + 'wiki_weapons' + ...Thanks for any help :D3 Answers1. If performance can be a problem, because tables have lots of rows, I would do this way. Grouping and counting first and joining tables next.SELECT characters.name, COALESCE(count_unlocks,0) AS unlocks, COALESCE(count_armors,0) AS armors, COALESCE(count_weapons,0) AS weapons, COALESCE(count_unlocks,0) + COALESCE(count_armors,0) + COALESCE(count_weapons,0) AS total FROM characters LEFT JOIN (SELECT wiki_items.character_id, count(*) AS count_unlocks from wiki_items GROUP BY wiki_items.character_id) AS wiki_unlocks ON characters.character_id = wiki_unlocks.character_id LEFT JOIN (SELECT wiki_armors.character_id, count(*) AS count_armors from wiki_armors GROUP BY wiki_armors.character_id) AS wiki_armors ON characters.character_id = wiki_armors.character_id LEFT JOIN (SELECT wiki_weapons.character_id, count(*) AS count_weapons from wiki_weapons GROUP BY wiki_weapons.character_id) AS wiki_weapons ON characters.character_id = wiki_weapons.character_id2. It's probably easiest to do each count as a subselect:SELECT c.name , (select COUNT(i.character_id) From wiki_items i Where c.character_id=i.character_id ) as wiki_unlocks , (select COUNT(a.character_id) From wiki_armors a Where c.character_id=a.character_id ) as wiki_armors , (select COUNT(w.character_id) From wiki_weapons w Where c.character_id=w.character_id ) as wiki_weapons FROM characters c3. Maybe it helps:SELECT Sum( a.count ) FROM( SELECT Count( * ) AS count FROM Table1 UNION ALL SELECT Count( * ) AS count FROM Table2 UNION ALL SELECT Count( * ) AS count FROM Table3 UNION ALL SELECT Count( * ) AS count FROM Table4 ) a内容来源于Database Administrators Stack Exchange, 遵循 CCBY-SA 4.0 许可协议进行翻译与使用。原文链接：Count multiple tables as one count