Classified by segment

  • Thread starter Thread starter Dawn
  • Start date Start date
D

Dawn

There’s a table, with the format:
Table1:price,with only one column.
I want to use query to Make it output as follows:
1.
Price in (0,50k) Price in (50k,1m) Price in (1m,5m) Price >5M
count

Or 2.
count
Price in (0,50k)
Price in (50k,1m)
Price in (1m,5m)
Price >5M
How to arrange query?pls give it in sql.
Many thanks.
 
I fail to see how your initial data is in the table. You probably over
simplified your problem when you say that table1 has ONLY one column.



Vanderghast, Access MVP
 
SELECT Abs(Sum(Price >0 and <=50000)) as Count0_50
, Abs(Sum(Price >50000 and <=10000000)) as Count50_1M
, Abs(Sum(Price >1000000 and <=5000000)) as Count1_5M
, Abs(Sum(Price >5000000)) as CountOver5M
FROM Table1

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Dear John,
As trying your way,I write the following:
SELECT ABS(SUM(QUERY4.[End of Day Bal LCY]>0 AND QUERY4.[End of Day Bal
LCY]<=100000) )AS SUM010K,
ABS(SUM(QUERY4.[End of Day Bal LCY]>100000 AND QUERY4.[End of Day Bal
LCY]<=300000) )AS SUM10K30K,
ABS(SUM(QUERY4.[End of Day Bal LCY]>300000) ) AS SUMOVER30K,
FROM QUERY4;
But while try to run it return with error, within the sentence ,where is
wrong? Thanks
Dawn
 
Looks as if you have an extra comma at the end of the SELECT clause.

SELECT ABS(SUM([End of Day Bal LCY]>0
AND [End of Day Bal LCY]<=100000) )AS SUM010K,

ABS(SUM([End of Day Bal LCY]>100000
AND [End of Day Bal LCY]<=300000) )AS SUM10K30K,

ABS(SUM([End of Day Bal LCY]>300000) ) AS SUMOVER30K

FROM QUERY4;

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Dear John,
As trying your way,I write the following:
SELECT ABS(SUM(QUERY4.[End of Day Bal LCY]>0 AND QUERY4.[End of Day Bal
LCY]<=100000) )AS SUM010K,
ABS(SUM(QUERY4.[End of Day Bal LCY]>100000 AND QUERY4.[End of Day Bal
LCY]<=300000) )AS SUM10K30K,
ABS(SUM(QUERY4.[End of Day Bal LCY]>300000) ) AS SUMOVER30K,
FROM QUERY4;
But while try to run it return with error, within the sentence ,where is
wrong? Thanks
Dawn


John Spencer said:
SELECT Abs(Sum(Price >0 and <=50000)) as Count0_50
, Abs(Sum(Price >50000 and <=10000000)) as Count50_1M
, Abs(Sum(Price >1000000 and <=5000000)) as Count1_5M
, Abs(Sum(Price >5000000)) as CountOver5M
FROM Table1

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
Click to expand...
Click to expand...
 
Dear John,
It works , thanks.
And what if I want both “the sum of balance “ ,â€the count “ two fields under
the classification ,for example [0,30k],(30k,+∞),how to write the sql?
Many thanks.
 
SELECT ABS(SUM([End of Day Bal LCY]>0
AND [End of Day Bal LCY]<=100000) )AS Count010K

.. SUM(IIF([End of Day Bal LCY]>0
AND [End of Day Bal LCY]<=100000,[End of Day Bal LCY],0)) as Total010K

, ABS(SUM([End of Day Bal LCY]>100000
AND [End of Day Bal LCY]<=300000) )AS Count10K30K

, SUM(IIF([End of Day Bal LCY]>100000
AND [End of Day Bal LCY]<=300000,[End of Day Bal LCY],0) as Total10K30K

, ABS(SUM([End of Day Bal LCY]>300000) ) AS CountOVER30K

, SUM([End of Day Bal LCY]>300000,[End of Day Bal LCY],0) as TotalOVER30K

FROM QUERY4;

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Whoops, there is a period that should be a comma and at least one missing
closing parentheses. Hopefully this one is syntactically correct.

SELECT ABS(SUM([End of Day Bal LCY]>0
AND [End of Day Bal LCY]<=100000) )AS Count010K

, SUM(IIF([End of Day Bal LCY]>0
AND [End of Day Bal LCY]<=100000,[End of Day Bal LCY],0)) as Total010K

, ABS(SUM([End of Day Bal LCY]>100000
AND [End of Day Bal LCY]<=300000) )AS Count10K30K

, SUM(IIF([End of Day Bal LCY]>100000
AND [End of Day Bal LCY]<=300000,[End of Day Bal LCY],0)) as Total10K30K

, ABS(SUM([End of Day Bal LCY]>300000) ) AS CountOVER30K

, SUM([End of Day Bal LCY]>300000,[End of Day Bal LCY],0) as TotalOVER30K

FROM QUERY4;



John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

John Spencer wrote:
SNIP out erroneous SQL statement
Dear John,
It works , thanks.
And what if I want both “the sum of balance “ ,â€the count “ two fields
under the classification ,for example [0,30k],(30k,+∞),how to write
the sql?
Many thanks.
Click to expand...
Click to expand...
 
Back
Top