SQL Join and Count

Question

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?

Answer 1

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;

Answer 2

I had to change your tables a little bit. Check if you can that the foreign keys relate to columns exactly the same data type to avoid conversions.

CREATE TABLE SUBJT (
    SUBJ_ID CHAR(4) PRIMARY KEY,
    SUBJ_NAME VARCHAR(25) NOT NULL,
    SUBJ_DESCRIPT VARCHAR(500)
)

--DROP TABLE ITEM

CREATE TABLE ITEM
(
    ITEM_ID CHAR(8) PRIMARY KEY,
    ITEM_NAME VARCHAR(25) NOT NULL,
    ITEM_DESCRIPT VARCHAR(500)
)


CREATE TABLE ITEM_SUBJECT (
    ITEM_ID CHAR(8),
    SUBJ_ID CHAR(4),
    PRIMARY KEY (ITEM_ID, SUBJ_ID),
    CONSTRAINT FK_01  FOREIGN KEY(ITEM_ID) REFERENCES ITEM(ITEM_ID),
    CONSTRAINT FK_02 FOREIGN KEY (SUBJ_ID) REFERENCES SUBJT(SUBJ_ID)
)


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 did it like this:

SELECT a.ITEM_ID, c.SUBJ_NAME
,count_ITEM_ID=count_big(a.ITEM_ID) as 
FROM LOAN AS a
INNER JOIN ITEM_SUBJECT AS b ON a.ITEM_ID = b.ITEM_ID
INNER JOIN SUBJT AS c ON b.SUBJ_ID = c.SUBJ_ID
GROUP BY a.ITEM_ID, c.SUBJ_NAME
ORDER BY C.SUBJ_NAME

make sure you have the right indexes, try to get rid of that sort operator:

Answer 3

If you want to see how many subj_names are there for a given item_id, you can go like this:

SELECT   a.ITEM_ID, 
         COUNT(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

However, in this case, you will only see an ITEM_ID and count of SUBJ_NAMEs. Example below:

ITEM_ID    COUNT_SUBJ_NAME
  1             3
  2             2
  3             1

If you would like to retain information about SUBJ_NAMEs you can use window aggregate function:

SELECT   a.ITEM_ID, 
         c.SUBJ_NAME,
         COUNT(c.SUBJ_NAME) OVER (PARTITION BY a.ITEM_ID) 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

This would preserve detail information (no GROUP BY clause), but still give you a count of subj_names for item_id. Example below:

ITEM_ID  SUBJ_NAME  COUNT_SUBJ_NAME
  1          a            3
  1          b            3
  1          c            3
  2          a            2
  2          b            2
  3          a            1