Solved

How to get this example out put with SQL

Posted on 2014-10-10
4
227 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 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

Technology Partners: 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

These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
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…

752 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