Classified by segment

[Dawn]

There’s a table, with the format:
Table1rice,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.

 

[Michel Walsh]

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

 

[John Spencer]

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
'====================================================

 

[Dawn]

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]

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

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
'====================================================

 

[Dawn]

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.

 

[John Spencer]

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]

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.