Shubham Ringne Shubham Ringne - 1 month ago 10
MySQL Question

Find Count of Column joined from sum of different column mysql

I have 2 tables:

table a

+--- -----+------------+-------------+
|session_id| product_id | orders |
+---------+------------+-------------+
| 1 | 11 | 0 |
| 1 | 22 | 2 |
| 1 | 34 | 1 |
| 2 | 11 | 0 |
| 3 | 43 | 0 |
| 3 | 11 | 1 |
+---+-----------------+-------------+


table b:

+--- -----+--------------+
|product_id |category_id |
+-----------+------------+-
| 11 | 100 |
| 12 | 101 |
| 34 | 102 |
| 22 | 103 |
| 43 | 104 |
| 13 | 105 |
+----------+--------------+


What is want is a table which consists of HOW MANY CATEGORY ID WERE THERE IN EACH SESSION ID AND ALSO TOTAL ORDERS PLACED IN THAT SESSION ID

+----------+------------+---------------+
|session_id| count(category_id) | orders|
+---------+------------+----------------+
| 1 | 3 | 3 |
| 2 | 1 | 0 |
| 3 | 2 | 1 |
+---+-----------------+-----------------+


I tried:

select a.session_id,count(b.category_id),sum(a.orders) from a
join table b
on a.product_id = b.product id


is this query right? please help me i am a beginner

Answer
    I tried with 2 methods    
    1)...SELECT a.session_id,COUNT(b.category_id),SUM(a.orders) FROM #a a
        LEFT JOIN  #b b
        ON a.product_id = b.product_id GROUP BY a.session_id
GROUP BY a.Session_ID

      2...)  SELECT D.SESSION_ID,COUNT(CATEGORY_ID),SUM(D.ORDERS) FROM #A D

        OUTER APPLY 
           ( 
           SELECT CATEGORY_ID,PRODUCT_ID FROM #B B 
           WHERE D.PRODUCT_ID = B.PRODUCT_ID
           ) A 
           GROUP BY D.SESSION_ID 
        GO