Solved

How to get this example out put with SQL

Posted on 2014-10-10
4
235 Views
Last Modified: 2014-10-10
Hi all,

I have bellow example :

day   | duration >29  | duration <10
1-october | 125456 | 465498
3-october | 255456 | 465447

I want to do this in sql/postgress I tried with sum(case when etc) but I having condition didn't work their, just one thing more that I want the total of duration like if someone connected to connection  3 times with 10 second so it should be come under ( duration >29) or if total duration of one connection would be greater 29 it will come under that as well.

thanks forhelp
0
Comment
Question by:hi4ppl
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40372649
at the moment I don't know how I could answer

please provide sample data
and the expected result relevant to that data
0
 
LVL 1

Author Comment

by:hi4ppl
ID: 40372670
Hi,

here is sample data and expected result

SAMPLE DATA
day |account|duration
1-october | 123|  10
1-october | 123| 10
1-october | 123| 10
1-october | 321 | 30
1-october | 456 | 9

Open in new window


and result looking for from above data

RESULT
day   | duration >29  | duration <10
1-october | 2 | 1

Open in new window

0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40372718
day isn't a great field name as that word is used in some functions and can get confusing so I have used "day"

I have also used just these 2 condtions, adjust as needed
<=10
 > 10

|                            DAY | LT_10 | GT_10 |
|--------------------------------|-------|-------|
| October, 01 2014 00:00:00+0000 |     1 |     2 |

Open in new window


select
      "day"
    , count(case when durn <= 10 then durn end) lt_10
    , count(case when durn  > 10 then durn end) gt_10
from (
      select
              account
            , "day"
            , sum(duration) durn
      from table1
      group by
              account
            , "day"
      ) s
group by
      "day"
;

Open in new window


If "day" contains time and you need to work only by day, then something like this may work for you:
select
      to_date( '' || yr || mn || dy,'YYYYMMDD') "day"
    , count(case when durn <= 10 then durn end) lt_10
    , count(case when durn  > 10 then durn end) gt_10
from (
      select
              account
            , date_part('year',day)  yr
            , date_part('month',day) mn
            , date_part('day',day)   dy
            , sum(duration)          durn
      from table1
      group by
              account
            , date_part('year',day)
            , date_part('month',day)
            , date_part('day',day)
      ) s
group by
      to_date( '' || yr || mn || dy,'YYYYMMDD')
;

Open in new window


test details
**PostgreSQL 9.3.1 Schema Setup**:

    CREATE TABLE Table1
    	("day" timestamp, "account" int, "duration" int)
    ;
    	
    INSERT INTO Table1
    	("day", "account", "duration")
    VALUES
    	('2014-10-01 00:00:00', 123, 10),
    	('2014-10-01 00:00:00', 123, 10),
    	('2014-10-01 00:00:00', 123, 10),
    	('2014-10-01 00:00:00', 321, 30),
    	('2014-10-01 00:00:00', 456, 9)
    ;

**Query 1**:

    select
          "day"
        , count(case when durn <= 10 then durn end) lt_10
        , count(case when durn  > 10 then durn end) gt_10
    from (
          select
                  account
                , "day"
                , sum(duration) durn
          from table1
          group by
                  account
                , "day"
          ) s
    group by
          "day"
    

**[Results][2]**:
    
    |                            DAY | LT_10 | GT_10 |
    |--------------------------------|-------|-------|
    | October, 01 2014 00:00:00+0000 |     1 |     2 |


**Query 2**:

    select
          to_date( '' || yr || mn || dy,'YYYYMMDD') "day"
        , count(case when durn <= 10 then durn end) lt_10
        , count(case when durn  > 10 then durn end) gt_10
    from (
          select
                  account
                , date_part('year',day)  yr
                , date_part('month',day) mn
                , date_part('day',day)   dy
                , sum(duration)          durn
          from table1
          group by
                  account
                , date_part('year',day)
                , date_part('month',day)
                , date_part('day',day)
          ) s
    group by
          to_date( '' || yr || mn || dy,'YYYYMMDD')
    

**[Results][3]**:
    
    |                            DAY | LT_10 | GT_10 |
    |--------------------------------|-------|-------|
    | October, 01 2014 00:00:00+0000 |     1 |     2 |


**Query 3**:

    select
          account
        , yr
        , mn
        , dy
        , count(case when durn <= 10 then durn end) lt_10
        , count(case when durn  > 10 then durn end) gt_10
    from (
          select
                  account
                , date_part('year',day)  yr
                , date_part('month',day) mn
                , date_part('day',day)   dy
                , sum(duration)          durn
          from table1
          group by
                  account
                , date_part('year',day)
                , date_part('month',day)
                , date_part('day',day)
          ) s
    group by
          account
        , yr
        , mn
        , dy
    

**[Results][4]**:
    
    | ACCOUNT |   YR | MN | DY | LT_10 | GT_10 |
    |---------|------|----|----|-------|-------|
    |     123 | 2014 | 10 |  1 |     0 |     1 |
    |     456 | 2014 | 10 |  1 |     1 |     0 |
    |     321 | 2014 | 10 |  1 |     0 |     1 |


**Query 4**:

    select ''|| 1 || 2 || 3 from table1
    

**[Results][5]**:
    
    | ?COLUMN? |
    |----------|
    |      123 |
    |      123 |
    |      123 |
    |      123 |
    |      123 |



  [1]: http://sqlfiddle.com/#!15/739c2/12

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40372723
one small request

if providing date/time information use a numeric date/time format that isn't language or culture specific

YYYY-MM-DD

or
YYYY-MM-DD hh:mm:ss
0

Featured Post

Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question