Solved

How to get this example out put with SQL

Posted on 2014-10-10
4
220 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
  • 3
4 Comments
 
LVL 48

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 48

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 48

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Read about achieving the basic levels of HRIS security in the workplace.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

856 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