CODEWITHSUNDEEP
mysqlsql
user2436631
user2436631

Reputation: 3

partial enumeration of rows with SQL/Mysql

I've been thinking/googling around this problem with no result... I'm trying to enumerate the results of a query BUT in a partial way, ie by having the counter reset for each new code. Here's and example, I also need a partial total count per code:

TABLE A:

CODE CUSTOMER
A     33
A     34
A     45
B     10
B     22
C     33

The following code gives me part of the result:

SELECT A.code, A.customer, B.total FROM A
INNER JOIN (SELECT code, count(*) AS total FROM A GROUP BY code) AS B
WHERE A.code=B.code

CODE   CUSTOMER TOTAL  PARTIAL
A      33        3      1
A      34        3      2
A      45        3      3
B      10        2      1
B      22        2      2
C      33        1      1

Except for the PARTIAL column counter of the codes....which I don't know how to generate....any ideas? Thanks!

Upvotes: 0

Views: 148

Answers (2)

John Woo
John Woo

Reputation: 263703

Here's a messy one since MySQL does not support Window Functions like any other RDBMS do. So one way of doing this is by using user variables.

SELECT  code, customer, total, RowNumber -- final columns
FROM
        (
            SELECT  code,
                    customer,
                    total,
                    @sum := if(@cde = code, @sum ,0) + 1 RowNumber,
                    @cde := code
            FROM    
                    ( -- this part is your original query
                        SELECT  A.code, 
                                A.customer, 
                                B.total 
                        FROM    TableName a
                                INNER JOIN 
                                (
                                    SELECT  code, count(*) AS total 
                                    FROM    TableName 
                                    GROUP   BY code
                                ) AS B ON A.code = B.code
                    ) d,
                     -- declaring of user variables
                    (select @cde := '', @sum := 0) vars
            ORDER   BY  code, customer
        ) s

OUTPUT

╔══════╦══════════╦═══════╦═══════════╗
║ CODE ║ CUSTOMER ║ TOTAL ║ ROWNUMBER ║
╠══════╬══════════╬═══════╬═══════════╣
║ A    ║       33 ║     3 ║         1 ║
║ A    ║       34 ║     3 ║         2 ║
║ A    ║       45 ║     3 ║         3 ║
║ B    ║       10 ║     2 ║         1 ║
║ B    ║       22 ║     2 ║         2 ║
║ C    ║       33 ║     1 ║         1 ║
╚══════╩══════════╩═══════╩═══════════╝

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269593

You can do this with variables in MySQL. However, you can also do it with standard SQL, using a correlated subquery:

select a.*,
       (select count(*)
        from A a1
        where a1.code = a.code and
              a1.customer <= a.customer
       ) as Partial
from A

Upvotes: 1

Related Questions