CODEWITHSUNDEEP
sql-servert-sql
Ben Vaughn
Ben Vaughn

Reputation: 17

How to count number of times a particular word appears in a column?

I have two tables:

tblArtwork has these columns: AritstFirstName, ArtistLastName, TitleOfPiece, Year, Style, Price

tblPurchasaes has these columns: CustNumber, DateOfPurchase, Amount, TitleOfPiece

I want to write a query that tells me if certain CustNumbers prefer certain Styles by analyzing their purchase history. There are thousands of CustNumbers that have purchased pieces of art having the same style. So, I want to count the number of times duplicate styles appear in each CustNumber's purchases. I have the following query:

with tblPreferredStyles as (
    select CustNumber, P.TitleOfPiece, Style
        , Count(Style) over (partition by CustNumber order by Style) as StyleTotal
    from tblPurchases as P
    join tblArtwork as A on P.TitleOfPiece = A.TitleOfPiece
)
select *
from tblPreferredStyles

which returns something like this:

CustNumber TitleOfPiece Style StyleTotal
1 Whatever1 StA 2
1 Whatever2 StA 2
1 Whatever3 StB 5
1 Whatever4 StB 5
1 Whatever5 StB 5

The results in the StyleTotal column aren't counting what I want, and I'm not sure why. I would like the results to run like this: 2, 2, 3, 3, 3 since the first two pieces have the same style, and the next three pieces have the same style.

Any help is appreciated!

Upvotes: 0

Views: 54

Answers (1)

Lamak
Lamak

Reputation: 70668

You just need to add Style to the PARTITION BY inside the OVER clause (and remove the ORDER BY):

WITH tblPreferredStyles AS (
SELECT 
    CustNumber, 
    P.TitleOfPiece, 
    Style,
    COUNT(Style) OVER(PARTITION BY CustNumber, style) as StyleTotal
FROM tblPurchases as P
INNER JOIN tblArtwork as A
    ON P.TitleOfPiece = A.TitleOfPiece
)
SELECT * 
FROM tblPreferredStyles;

Upvotes: 1

Related Questions