Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to get this example out put with SQL

Posted on 2014-10-10
4
Medium Priority
?
247 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 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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

783 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