CODEWITHSUNDEEP
sqlpostgresql
ian-campbell
ian-campbell

Reputation: 1665

How can I combine rows and add a column in SQL table?

I have this table which shows the number of 200 and 404 responses from a website for each day. I need to rearrange it so each date is displayed once per row, and the status codes become the columns.

    status     |           date         |  num  
---------------+------------------------+-------
 200 OK        | 2016-07-31 00:00:00+00 | 45516
 404 NOT FOUND | 2016-07-31 00:00:00+00 |   329
 200 OK        | 2016-07-30 00:00:00+00 | 54676
 404 NOT FOUND | 2016-07-30 00:00:00+00 |   397
 200 OK        | 2016-07-29 00:00:00+00 | 54569
 404 NOT FOUND | 2016-07-29 00:00:00+00 |   382
 200 OK        | 2016-07-28 00:00:00+00 | 54404
 404 NOT FOUND | 2016-07-28 00:00:00+00 |   393

What I'm ultimately trying to find out is the percentage of responses which are 404 on each day. Something like select (date.200 / date.404) from mytable

Upvotes: 0

Views: 48

Answers (4)

Nick
Nick

Reputation: 2513

What you actually need is called "Pivot Table" and usually it's achieved in Postgres by using "tablefunc" extension https://www.postgresql.org/docs/current/static/tablefunc.html.

But in this simple scenario I'd choose plain SQL approach with "manual pivoting" (like described here http://tapoueh.org/blog/2013/07/simple-case-for-pivoting-in-sql/):

select
  date,
  sum(case when left(status, 3) = '200' then num end) as status200,
  sum(case when left(status, 3) = '404' then num end) as status404
from
  log
group by 1
order by 1 desc;

Notice, that sum() allows you to have multiple records with the same day and same status -- but with your table structure this will also work.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

SELECT
    "date"::date,
    100.0 * SUM(CASE WHEN status LIKE '404%' THEN num ELSE 0 END) / SUM(num) AS percentage
FROM yourTable
GROUP BY "date"::date

Output:

enter image description here

Demo here:

Rextester

Upvotes: 1

Maverick Sachin
Maverick Sachin

Reputation: 883

Try this query --

;WITH PivotData
AS (
    SELECT [Date], -- grouping element
        [Status], -- spreading element
        Number -- aggregating element
    FROM StatusEntries
    )
SELECT [Date]
    ,[200 OK]
    ,[404 NOT FOUND]
FROM PivotData
Pivot(Sum(Number) FOR [Status] IN (
            [200 OK]
            ,[404 NOT FOUND]
            )) AS P;

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

You could use a join 

 select a.date, a.num num_200, b.num num_404, a.num num_200/b.num num_404 rate
 from my_table a
 left join my_table b on a.date = b.date  and b.status= '404 NOT FOUND'
 where a.status= '200 OK'

Upvotes: 0

Related Questions