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

Question

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

Tim Biegeleisen · Accepted Answer · 2017-06-23 05:56:21Z

1

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:

Demo here:

Rextester

edited Jun 23, 2017 at 5:56

answered Jun 23, 2017 at 5:26

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Lostfields Over a year ago

each day, so you may want to group by the date as well.

Tim Biegeleisen Over a year ago

@ian-campbell The query works fine in the demo. Are you sure that you have the right table name etc.?

ian-campbell Over a year ago

None of the percentages should be anywhere close to 50% though.

Tim Biegeleisen Over a year ago

@ian-campbell They are all 50% using the 8 rows of sample data you provided.

ian-campbell Over a year ago

The percentage of 404 codes on July 31 is 0.72%. Dividing 329 by 45516

|

Nick · Accepted Answer · 2017-06-23 06:10:07Z

1

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.

answered Jun 23, 2017 at 6:10

Nick

2,54315 silver badges21 bronze badges

Comments

ScaisEdge · Accepted Answer · 2017-06-23 05:32:55Z

0

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'

answered Jun 23, 2017 at 5:32

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Comments

Maverick Sachin · Accepted Answer · 2017-06-23 05:37:13Z

0

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;

answered Jun 23, 2017 at 5:37

Maverick Sachin

8837 silver badges13 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Rextester

9 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

9 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related