Link to home
Start Free TrialLog in
Avatar of woodje
woodjeFlag for United States of America

asked on

SQL query trying to sum one fields with three different criteria

I have a table that I am trying to pull the phone # and then sum usage for three different date ranges. However I have a sytex problem and for the life of me I can not find it. Please let me know your thoughts. I have included the SQL code below. Thanks.
SELECT
 
Phone#,
(select 
	SUM(D_USG_B)
	where D_USG_DT between 1080408 and 1080505) as Prior,
(select 
	SUM(D_USG_B) 
	where D_USG_DT between 1080506 and 1080602) as During,
(select 
	SUM(D_USG_B)
	where D_USG_DT between 1080603 and 1080701) as Post	
 
 
FROM X_USAGE_V
 
WHERE Phone# = '9995551212'
 
 
Group by
	1

Open in new window

Avatar of chapmandew
chapmandew
Flag of United States of America image

not sure about the PL/SQL world, but this shoudl work in the sql server 2005 world:

SELECT
 
Phone#,
prior = sum(case when D_USG_DT between 1080408 and 1080505 then d_usg_b else 0 end),
during = sum(case when D_USG_DT between 1080506 and 1080602 then d_usg_b else 0 end),
post = sum(case when D_USG_DT between 1080603 and 1080701 then d_usg_b else 0 end)
FROM X_USAGE_V
WHERE Phone# = '9995551212'
Group by
        phone#
Avatar of pzozulka
pzozulka

In each subquery you still have to follow the basic syntax of (Select, From, Where).
So in each subquery, specify the following:

SELECT
 
Phone#,
(select 
        SUM(D_USG_B) From Table
        where D_USG_DT between 1080408 and 1080505) as Prior,
(select 
        SUM(D_USG_B) From Table
        where D_USG_DT between 1080506 and 1080602) as During,
(select 
        SUM(D_USG_B) From Table
        where D_USG_DT between 1080603 and 1080701) as Post     
 
 
FROM X_USAGE_V
 
WHERE Phone# = '9995551212'
 
 
Group by
        1

Open in new window

Not sure about the Oracle aspects, but SQL will normally require you to put square brackets [ ] around any object name that has a punctuation character other than underscore _, so Phone# will need to be [Phone#]

Otherwise, give this a whirl (air code)..

SELECT
   [Phone#],
   CASE WHEN D_USG_DT between 1080408 and 1080505 THEN SUM(D_USG_B) END as Prior,
   CASE WHEN D_USG_DT between 1080506 and 1080602 THEN SUM(D_USG_B) END as During,
   CASE WHEN D_USG_DT between 1080603 and 1080701 THEN SUM(D_USG_B) END as Post
FROM X_USAGE_V
WHERE [Phone#] = '9995551212'
jim, i don't think your query is syntactically correct.
use following query
SELECT
 
Phone#,
(select 
	SUM(D_USG_B) FROM X_USAGE_V
	where D_USG_DT between 1080408 and 1080505 and Phone# = '9995551212') as Prior,
(select 
	SUM(D_USG_B) FROM X_USAGE_V
	where D_USG_DT between 1080506 and 1080602 and Phone# = '9995551212') as During,
(select 
	SUM(D_USG_B) FROM X_USAGE_V
	where D_USG_DT between 1080603 and 1080701 and Phone# = '9995551212') as Post	
 
 
FROM X_USAGE_V
 
WHERE Phone# = '9995551212'
 
Group by	1

Open in new window

IF you use 3 subqueries, you're just making your statement unnecessarily slow.
try this one
SELECT
 [Phone#],
 
SUM(case when D_USG_DT between 1080408 and 1080505 then D_USG_B else 0 end) as Prior,
 
SUM(case when D_USG_DT between 1080506 and 1080602then D_USG_B else 0 end) as During,
 
SUM(case when D_USG_DT between 1080603 and 1080701 then D_USG_B else 0 end) as Post
 
FROM X_USAGE_V
 
WHERE  [Phone#] = '9995551212'
  
Group by
 [Phone#]

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of bashka_abdyli
bashka_abdyli
Flag of Albania image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of woodje

ASKER

Thank you everyone for your responses. "jimhorn" yours seems to be moving me in the right direction. The field Phone# is just naming in this sample. The true field has no characters. When I use your code and fun it against the table I am getting an errror of "Selected non-aggregate values must be part of the associated group." any ideas on how to correct this? When I add the Group By clause the error changes to "GROUP BY and WITH...BY clauses may not contain aggregate functions."
try my first comment.
Avatar of woodje

ASKER

bashka_abdyli,

I am trying your code now. I will post back with the results.
bashka_abdyli's cod3 is the same code as my first comment.
Avatar of woodje

ASKER

This worked perfectly thank you for your fast response.
Was about to post an entry, did a refresh and found it had been answered - but what gives ? The very first posting is the correct entry and clearly copied by bashka_abdyli ? It is not the best way to win friends and influence people (or experts upon which you ask for assistance)... chapmandew is correct - wow...