woodje
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
In each subquery you still have to follow the basic syntax of (Select, From, Where).
So in each subquery, specify the following:
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
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'
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
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#]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
bashka_abdyli,
I am trying your code now. I will post back with the results.
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.
ASKER
This worked perfectly thank you for your fast response.
wow.
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...
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#