jad jad - 5 months ago 4
SQL Question

mysql query select distinct value from two table with the count of sum value in two table

I have two tables in db

1- i want to select distinct value from 2 tables

2- i want to print the number of each value

Ex: if i have on t1

t1
--------------
a

a

a

b


t2:

t2
--------------
a

b

c


the result will be:

a (4)

b (2)

c (1)


i try this but it not what i want

$sql=mysqli_query($conn,"select db_shopname from tbl_order UNION
SELECT db_shopname FROM tbl_item order by db_shopname asc")
or die(mysqli_error($conn));
$count=mysqli_num_rows($sql);
while($res=mysqli_fetch_array($sql)){
echo $res['db_shopname'];echo $count ;echo"<br/>";
}

Answer

You need UNION ALL instead of UNION. That way you merge the values from 2 tables into 1 virtual table. Then you can count the number of individual values using GROUP BY clause. Example:

select f, COUNT(f) as countf FROM
(select t1.f from t1 union all select t2.f from t2) t
GROUP BY f

SQL Fiddle

In PHP you can then use $res['countf'] to print the count