How I can count identical rows in sql

Question

I have 2 tables.

Table transaction:

id | customer_id | department_id
--------------------------------
1  |      1      |      2
--------------------------------
2  |      2      |      3
--------------------------------
3  |      2      |      4
--------------------------------
4  |      3      |      1
--------------------------------
5  |      2      |      3
--------------------------------

Table des_department

id |   caption 
-----------------
1  |  department1  
-----------------
2  |  department2
-----------------
3  |  department3
-----------------
4  |  department4
-----------------
5  |  department5
-----------------

I need to display department caption for each customer_id. Choose department that user visits more than others.

Example of ouput:

    customer_id  |      caption
    ------------------------------
          1      |     department2
    ------------------------------
          2      |     department3
    ------------------------------
          3      |     department1
    ------------------------------

I have also own query. But I display number of all visits.

My query:

    SELECT t.customer_id, t.terminal_i
  FROM transaction t WHERE (t.customer_id, t.terminal_id) IN
  ( SELECT t1.customer_id, t1.terminal_id
    FROM transaction t1 GROUP BY t1.customer_id
  )

I work on dbForge for Mysql.

possible duplicate of [SQL Select only rows with Max Value on a Column](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Cwt, May 14 '15 at 10:50
@sevenforce Your link is about display only max rows. In my example I need to count for each user his visited department, and show in which department user visits more than others. — Шыназ Алиш, May 14 '15 at 12:08

score 1 · Answer 1 · answered May 14 '15 at 10:53

This type of query is unnecessarily cumbersome in MySQL (because MySQL supports neither common table expressions nor window functions).

One approach is to aggregate the table twice, once to get the count per customer/department. The second time to get the maximum per customer. Then join this back to the table aggregated by customer and department to get the maximum department value.

SELECT t.customer_id, d.caption
FROM (SELECT td.customer_id, MAX(cnt) as maxcnt
      FROM (SELECT t.customer_id, t.deparment_id, COUNT(t.terminal_id) as cnt
            FROM transaction t 
            GROUP BY t.customer_id, t.department_id
           ) td
      GROUP BY t.customer_id
     ) tt JOIN
     (SELECT t.customer_id, t.deparment_id, COUNT(t.terminal_id) as cnt
      FROM transaction t 
      GROUP BY t.customer_id, t.deparment_id
     ) td
     ON t.customer_id = tt.customer_id and td.cnt = tt.cnt JOIN
     des_department d
     ON td.department_id = d.id

Sorry, I confused you. terminal_id is the same this department_id I only explained with other word. I correct in my example too, could your correct your answer pls. — Шыназ Алиш, May 14 '15 at 12:02

score 0 · Accepted Answer · answered May 15 '15 at 04:31

I write this query by researching in the internet, and may be it will be helpfull for someone. Here is sql code:

SELECT customer_id, dd.caption 
  FROM 
  (SELECT t.customer_id, t.deparment_id
  FROM (SELECT t.customer_id, t.deparment_id, COUNT(t.deparment_id) AS ter
        FROM transaction t
        GROUP BY t.customer_id, t.deparment_id) t
  WHERE NOT EXISTS (SELECT 1
                    FROM (SELECT t.customer_id, t.deparment_id, COUNT(t.deparment_id) AS ter
                          FROM transaction t
                          GROUP BY t.customer_id, t.deparment_id) td
                          WHERE td.customer_id = t.customer_id AND td.ter>t.ter
                          ))
  tx
  INNER JOIN des_department dd
  ON tx.deparment_id = dd.id

How I can count identical rows in sql

2 Answers2