I want to partition my table by the last name?

Question

I want to be able to count and then partition my table by count over the C.LastName. I am just learning SQL and am not completely certain how the COUNT(...) line works in the program.

Is this the correct way to count and partition the table on the LastName?

For some reason this line of code in the SQL just seems to be a difficult beast to see how it completely works.

 select C.*,
        COUNT(*) over (partition by c.LastName) as '--Count--'
 from Customer C;

Table:

PrimaryKey  FirstName   LastName    MiddleName  PhoneNumber Address1    Address2    City    State   Zipcode
1   Test1                   LTest1                  MTest1                  5555555555      555 Governors                                       5555 Governors Avenue                               Pierre      TT          99999
2   Test2                   LTest2                  MTest2                  4444441234      444 Governors Street                                4444 Governors Blvd                                 Bill        PP          44444
3   Test3                   LTest3                  MTest3                  3333331234      333 Governors Street                                3333 Governors Street                               Pop         RR          33333
4   Test1                   LTest1                  MTest9                  7777777777      77 Governors Avenue                                 7 Governors Street                                  Yet         UU          22222
10  FirstName               LastNme                 MiddleName              8888888889      321 H Street                                        4321 U Street                                       TownUSA     VB          77777
11  NULL    A                       NULL    NULL    NULL    NULL    NULL    NULL    NULL
12  NULL    A                       NULL    NULL    NULL    NULL    NULL    NULL    NULL
13  NULL    A                       LTest                   NULL    HTest                                               NULL    NULL    NULL    NULL
14  Test14                  LTest14                 MTest14                 1231231234      123 VVV                                             Billings                                            Billings    NULL    NULL
15  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
16  NULL    A                       NULL    NULL    NULL    NULL    NULL    NULL    NULL
17  NULL    A                       NULL    NULL    NULL    NULL    NULL    NULL    NULL
18  NULL    A                       NULL    NULL    NULL    NULL    NULL    NULL    NULL
19  NULL    test                    NULL    NULL    NULL    NULL    NULL    NULL    NULL
20  NULL    NULL    NULL    1               123 ave                                             123 ave                                             NULL    NULL    NULL

Customer Table:

1   Test1                   LTest1                  MTest1                  5555555555      555 Governors                                       5555 Governors Avenue                               Pierre      TT          99999
2   Test2                   LTest2                  MTest2                  4444441234      444 Governors Street                                4444 Governors Blvd                                 Bill        PP          44444
3   Test3                   LTest3                  MTest3                  3333331234      333 Governors Street                                3333 Governors Street                               Pop         RR          33333
4   Test1                   LTest1                  MTest9                  7777777777      77 Governors Avenue                                 7 Governors Street                                  Yet         UU          22222
10  FirstName               LastNme                 MiddleName              8888888889      321 H Street                                        4321 U Street                                       TownUSA     VB          77777
11  NULL    A                       NULL    NULL    NULL    NULL    NULL    NULL    NULL
12  NULL    A                       NULL    NULL    NULL    NULL    NULL    NULL    NULL
13  NULL    A                       LTest                   NULL    HTest                                               NULL    NULL    NULL    NULL
14  Test14                  LTest14                 MTest14                 1231231234      123 VVV                                             Billings                                            Billings    NULL    NULL
15  NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
16  NULL    A                       NULL    NULL    NULL    NULL    NULL    NULL    NULL
17  NULL    A                       NULL    NULL    NULL    NULL    NULL    NULL    NULL
18  NULL    A                       NULL    NULL    NULL    NULL    NULL    NULL    NULL
19  NULL    test                    NULL    NULL    NULL    NULL    NULL    NULL    NULL
20  NULL    NULL    NULL    1               123 ave                                             123 ave   

                                        NULL    NULL    NULL

New Query:

SELECT C.*,
        A.B as "--Count--"
FROM Customer c
INNER JOIN
(
  SELECT lastname,
         count(*) as B
  FROM customer
  GROUP BY lastname
) A
ON c.lastname = A.lastname;

Thank you below for showing me this example. Is the count(*) as B (I changed some of the variables just so that i could see that you could. A and B would not be good variable names to use.

Does the count(*) as B refer to the lastname in that select statement. This is the only statment that I am not really certain about.

What is your question? You seem to be on the right track syntactically. — Gordon Linoff, May 02 '14 at 14:48
My question is why does the SQL statement not "compile" and run. — Doug Hauf, May 02 '14 at 15:46

HoneyBadger · Answer 1 · 2014-05-02T15:20:05.330

2

Your syntax is correct, if you want to count people with the same last name. Those windowed functions can seem a bit daunting at first, but just play around with it, and look at your results and you should be fine. The fact that you post the question suggests you get a different result then expected, is that right?

Edit: I jumped to conclusions and assumed sqlserver...

edited May 02 '14 at 15:20

answered May 02 '14 at 15:05

HoneyBadger

14,750
3
34
48

score 2 · Accepted Answer · edited May 23 '17 at 11:57

2

If you are using MySQL, COUNT(*) is not a supported analytic function. You can use the following query to get the same result:

SELECT 
    C.*,
    counts_lastname.cnt_lastname as "--Count--"
FROM Customer c
INNER JOIN
(
  SELECT 
  lastname,
  count(*) as cnt_lastname
  FROM customer
  GROUP BY lastname
) counts_lastname
ON c.lastname = counts_lastname.lastname;

References:

Analytic functions: SUM, AVG, ROW_NUMBER on EXPLAIN EXTENDED blog

A related question on SO

edited May 23 '17 at 11:57

Community

1
1

answered May 02 '14 at 15:16

Joseph B

5,519
1
15
19

COUNT(*) over (partition by c.LastName) as '--Count--' will not work then in SQL Server. – Doug Hauf May 02 '14 at 15:46
1

It does work in SQL Server. Please see this fiddle - http://www.sqlfiddle.com/#!6/3aada/6 What error / output do you get? – Joseph B May 02 '14 at 15:50

I want to partition my table by the last name?

2 Answers2