How to get this example out put with SQL

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
LVL 1
hi4pplAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
at the moment I don't know how I could answer

please provide sample data
and the expected result relevant to that data
0
hi4pplAuthor Commented:
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
PortletPaulfreelancerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.