CODEWITHSUNDEEP
sqlsql-serverinner-join
gnome
gnome

Reputation: 1133

SQL Count with inner joins

I have this query

SELECT semester, COUNT(id) AS total, RIGHT(RTRIM(semester), 4) AS year 
FROM TextbookReservation 
WHERE (semester IS NOT NULL) 
AND (semester <> '')
AND (semester <> 'SM2008') 
AND semester <> 'SU2008') 
GROUP BY semester ORDER BY year, semester DESC

Which produces something like this:

semester  total year
SP2006    2277  2006
FA2006    4367  2006
SP2007    2893  2007
FA2007    5624  2007
SP2008    4083  2008
FA2008    6451  2008

I would like to return something like this:

year  totalSpring totalFall
2006  2277        4367
2007  2893        5624
2008  4083        6451

Any ideas on how I can approach this query? Thanks!

Upvotes: 0

Views: 585

Answers (3)

daniloquio
daniloquio

Reputation: 3902

Simplified version of the other answers:

SELECT 
    RIGHT(RTRIM(semester), 4) year, 
    COUNT(CASE WHEN left(semester,2)='SP' then id else null end) totalSpring,
    COUNT(CASE WHEN left(semester,2)='FA' then id else null end) totalFall
FROM TextbookReservation
WHERE (semester IS NOT NULL) 
      AND (semester <> '')
      AND (semester <> 'SM2008') 
      AND (semester <> 'SU2008') 
GROUP BY RIGHT(RTRIM(semester), 4)
ORDER BY RIGHT(RTRIM(semester), 4) DESC

Here, the test script:

CREATE TABLE #TextbookReservation
(
    semester VARCHAR(6),
    id INT  
)

INSERT INTO #TextbookReservation VALUES ('SP2006', 1)
INSERT INTO #TextbookReservation VALUES ('SP2006', 2)
INSERT INTO #TextbookReservation VALUES ('SP2006', 3)
INSERT INTO #TextbookReservation VALUES ('SP2006', 4)
INSERT INTO #TextbookReservation VALUES ('SP2006', 5)
INSERT INTO #TextbookReservation VALUES ('FA2006', 1)
INSERT INTO #TextbookReservation VALUES ('FA2006', 2)
INSERT INTO #TextbookReservation VALUES ('FA2006', 3)
INSERT INTO #TextbookReservation VALUES ('SP2007', 1)
INSERT INTO #TextbookReservation VALUES ('SP2007', 2)
INSERT INTO #TextbookReservation VALUES ('FA2007', 1)
INSERT INTO #TextbookReservation VALUES ('FA2007', 2)
INSERT INTO #TextbookReservation VALUES ('FA2007', 3)
INSERT INTO #TextbookReservation VALUES ('FA2007', 4)
INSERT INTO #TextbookReservation VALUES ('FA2007', 5)

SELECT 
    RIGHT(RTRIM(semester), 4) year, 
    COUNT(CASE WHEN left(semester,2)='SP' then id else null end) totalSpring,
    COUNT(CASE WHEN left(semester,2)='FA' then id else null end) totalFall
FROM #TextbookReservation
WHERE (semester IS NOT NULL) 
      AND (semester <> '')
      AND (semester <> 'SM2008') 
      AND (semester <> 'SU2008') 
GROUP BY RIGHT(RTRIM(semester), 4)
ORDER BY RIGHT(RTRIM(semester), 4) DESC


DROP TABLE #TextbookReservation

Upvotes: 0

Icarus
Icarus

Reputation: 63956

Without PIVOT tables, since you didn't specify the version:

SELECT [year], 
sum(case WHEN left(semester,2)='SP' then total ELSE 0 END) as totalSpring ,
sum(case WHEN left(semester,2)='FA' THEN total else 0 end) as totalFall
from (

       SELECT semester
            , count(id) AS total
            , right(RTRIM(semester), 4) AS year
       FROM
           TextbookReservation
       WHERE
           semester IS NOT NULL
           AND semester <> ''
           AND semester <> 'SM2008'
           AND semester <> 'SU2008'
       GROUP BY
           semester 
) t
GROUP BY t.[year]
order by t.[year]

Upvotes: 3

Doug Chamberlain
Doug Chamberlain

Reputation: 11341

What you would like to do is create a pivot. Version 2005 and greater SQL has a built in command to pivot data.

Can you please specify which version of SQL Server your database is using?

PS. You can also do this with Case statements pre 2005. since your example only has two discrete values for semesters I'll provide an answer using that method.

 Select year, SUM(TotalSpring), SUM(TotalFall) From
(select RIGHT(RTRIM(semester), 4) AS year, case LEFT(semester,2) when  'SP' then 1 else 0 end TotalSpring, case LEFT(semester,2) when  'FA' then 1 else 0 end TotalFall
from TextbookReservation
WHERE (semester IS NOT NULL) 
AND (semester <> '') 
AND (semester <> 'SM2008') 
AND semester <> 'SU2008') as t1
group by year
order by year

Upvotes: 2

Related Questions