CODEWITHSUNDEEP
sql
Sayed Fathy
Sayed Fathy

Reputation: 53

SQL divide results into 2 columns

I have a question about retrieving data using SQL

i want to get 

count(comments_id) where comment_year = 2012 and 
count(comments_id) where comment_year = 2013

in one query to get the output like this:

year12_comments  |  year13_comments
7                |  8

I hope its clear.

Upvotes: 2

Views: 676

Answers (3)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

Like this

...
SUM(CASE WHEN comment_year = 2012 THEN 1 ELSE 0 END) AS year12_comments,
SUM(CASE WHEN comment_year = 2013 THEN 1 ELSE 0 END) AS year13_comments,
...

Update:

If you want to get the percentage between them, you can enclose them in a subquery like this:

SELECT 
   (year12_comments / year13_comments) * 100
FROM
(
  SELECT
     ...
     SUM(CASE WHEN comment_year = 2012 THEN 1 ELSE 0 END) AS year12_comments,
     SUM(CASE WHEN comment_year = 2013 THEN 1 ELSE 0 END) AS year13_comments,
     ...

 ) sub

Update2

If you want to make the year12_comments, year13_comments as well as the percentage new column, just include them in the SELECT statement like this:

SELECT 
  year12_comments, 
  year13_comments,
  (year12_comments / year13_comments) * 100 AS ThePercenatge 
FROM
(
  SELECT
     ...
     SUM(CASE WHEN comment_year = 2012 THEN 1 ELSE 0 END) AS year12_comments,
     SUM(CASE WHEN comment_year = 2013 THEN 1 ELSE 0 END) AS year13_comments,
     ...

) sub

Upvotes: 4

ppeterka
ppeterka

Reputation: 20726

Unless you can use the PIVOT function @HamletHakobyan suggested, IMHO this is a bad decision. Unless (another) end of the World happens in 2013, you'll have to do this for 2014 too. I'd rather go with a simple group by for the user and the year too:

SELECT user_id, comment_year, count(1)
FROM mytable
GROUP BY user_id, comment_year

Sure, you'll have to code a bit in your application, but from a clarity aspect, this seems to be the way to go...

(BTW I think this will be quicker than the CASE WHEN structure too...)

Upvotes: 0

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

COUNT(CASE WHEN comment_year = 2012 THEN 1 END) year12_comments,
COUNT(CASE WHEN comment_year = 2013 THEN 1 END) year13_comments,

If you are using MS SQL Server 2005 or above, you can use PIVOT

Upvotes: 0

Related Questions