-1

I am trying to get the count of ids from one table with left join in a mysql query. it works well when i have one count. but when i try to add an additional count the result of the second count is the same as first count. so how to fix this query to have two counts. note: 1 st count result should be based on join condition 2 nd count result should be over all count not based on join

sai
  • 1
  • 2
    can you post the code / query ? – Devsi Odedra Sep 25 '19 at 13:32
  • 2
    Make it easy to assist you: [mcve] – jarlh Sep 25 '19 at 13:33
  • This seems likely to be a common error where people want some joins, each possibly involving a different key, of some subqueries, each possibly involving join and/or aggregation, but they erroneously try to do all the joining then all the aggregating or to aggregate over previous aggregations. [Strange duplicate behavior from GROUP_CONCAT of two LEFT JOINs of GROUP_BYs](https://stackoverflow.com/a/45252750/3404097) – philipxy Sep 26 '19 at 19:43
  • Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS/product & DDL, which includes constraints & indexes & tabular-formatted base table initialization. PS Please use standard language & punctuation. – philipxy Sep 26 '19 at 19:44

1 Answers1

0
SELECT COUNT(*)

counts all rows.

SELECT COUNT(column_name)

counts just the values that are not NULL in that particular column.

So in your case your first count should be COUNT(a column from your joined table) and your second count should be COUNT(*).

For special cases you can also use boolean expressions. For example

SELECT SUM(my_column = 'foo')

counts just the values where the value in my_column is foo, because the boolean expression returns 1 if true and 0 otherwise.

fancyPants
  • 50,732
  • 33
  • 89
  • 96