Sql-server – SQL Join and Count

countjoin;sql server

I have 3 tables in SQLServer that I want to join so I can count the number of times each SUBJ_NAME occurs for the ITEM_IDs in the LOAN table.

CREATE TABLE SUBJT (
    SUBJ_ID CHAR(4) PRIMARY KEY,
    SUBJ_NAME VARCHAR(25) NOT NULL,
    SUBJ_DESCRIPT VARCHAR(500)
)
CREATE TABLE ITEM_SUBJECT (
    ITEM_ID CHAR(8),
    SUBJ_ID CHAR(4),
    PRIMARY KEY (ITEM_ID, SUBJ_ID),
    FOREIGN KEY (ITEM_ID) REFERENCES ITEM,
    FOREIGN KEY (SUBJ_ID) REFERENCES SUBJT
)
CREATE TABLE LOAN (
    LOAN_ID INT PRIMARY KEY,
    PAT_ID INT NOT NULL,
    ITEM_ID CHAR(8) NOT NULL,
    VOL_ID INT NOT NULL,
    ITEM_OUTD DATE,
    ITEM_RTD DATE,
    ITEM_REN INT
    FOREIGN KEY (PAT_ID) REFERENCES PATRON,
    FOREIGN KEY (ITEM_ID) REFERENCES ITEM,
    FOREIGN KEY (VOL_ID) REFERENCES VOLUNTEER
)

I joined the tables with this query:

SELECT a.ITEM_ID, c.SUBJ_NAME
FROM LOAN AS a
JOIN ITEM_SUBJECT AS b ON a.ITEM_ID = b.ITEM_ID
JOIN SUBJT AS c ON b.SUBJ_ID = c.SUBJ_ID
ORDER BY SUBJ_NAME

But how do I add the count?

Best Answer

This should do it:

SELECT   a.ITEM_ID, 
         c.SUBJ_NAME,
         COUNT_BIG(c.SUBJ_NAME) AS COUNT_SUBJ_NAME
FROM     LOAN AS a
JOIN     ITEM_SUBJECT AS b
    ON a.ITEM_ID = b.ITEM_ID
JOIN     SUBJT AS c
    ON b.SUBJ_ID = c.SUBJ_ID
GROUP BY a.ITEM_ID. c.SUBJ_NAME;
Related Question