MYSQL - Get data from INNER JOIN with two tables

Question

I Have two tables for get impression and click

Product
+----+--------+
| PID| brand  |
+----+--------+
|  1 | Ford   |
|  2 | Toyota |
|  6 | Holden |
+----+--------+

States
+----+--------+------+--------+------------+
| ID | PID    |CLICKS| VIEWS  | DATE       |
+----+--------+------+--------+------------+
|  1 | 1      |   1  |   0    |  12/12/2015|
|  1 | 1      |   1  |   0    |  12/12/2015|
|  2 | 2      |   1  |   0    |  12/12/2015|
|  3 | 2      |   0  |   1    |  12/12/2015|
|  3 | 1      |   0  |   1    |  12/12/2015|
+----+--------+------+--------+------------+

I need to get some result like this

+--------+------+--------+
| PID    |CLICKS| VIEWS  |
+--------+------+--------+
| 1      |   2  |   1    |
| 2      |   1  |   0    |
+--------+------+--------+

is it possible? i have tried too many times its coming faults data

SELECT stats_property.date, property.idproperty, COUNT(stats_property.clicks='1') AS A, COUNT(stats_property.impression='0') AS I FROM property INNER JOIN stats_property ON property.idproperty = stats_property.idproperty group by property.idproperty, stats_property.clicks I Have try this way its not working — user1750832
– user1750832, Commented Apr 14, 2016 at 13:09

StanislavL · Accepted Answer · 2016-04-14 12:52:08Z

1

SELECT
  PID,
  SUM(CLICKS) as CLICKS,
  SUM(VIEWS) as VIEWS
FROM States
GROUP BY PID

If you need 0 for all Products

SELECT
  Products.PID,
  SUM(CLICKS) as CLICKS,
  SUM(VIEWS) as VIEWS
FROM Products 
     LEFT JOIN States ON States.PID=Products.PID
GROUP BY Products.PID

answered Apr 14, 2016 at 12:52

StanislavL

57.5k9 gold badges75 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Samir Sheikh · Accepted Answer · 2016-04-14 13:40:32Z

1

Use this by Inner Join.

SELECT t1.pid, sum(clicks) as CLICKS, sum(views) as VIEWS
FROM Product as t1 INNER JOIN States as t2 ON t1.pid=t2.pid GROUP BY t1.pid

edited Apr 14, 2016 at 13:40

answered Apr 14, 2016 at 13:35

Samir Sheikh

2,4522 gold badges22 silver badges40 bronze badges

1 Comment

StanislavL Over a year ago

INNER JOIN is futile. States table is enough. If all products are necessary then OUTER JOIN should be used

Collectives™ on Stack Overflow

MYSQL - Get data from INNER JOIN with two tables

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related