Databases
StackOverflow2013
Postsdbo
POsumming up tier levels in SQL without cursor
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POsumming up tier levels in SQL without cursor
    primarykey
    data
    text
    <p>I have a SQL server table that looks like this: </p> <pre><code>CREATE TABLE foo( PlayerID BIGINT NOT NULL UpdatedAt DATETIME NOT NULL CurrencyBal MONEY NOT NULL) </code></pre> <p>The table contains 30 records:</p> <pre><code>1,2012-05-10,300 2,2012-05-10,1100 3,2012-05-10,10000 </code></pre> <p>I would like to create a query that would return the total number of players, the date the query was run, and a breakdown of Currency Tiers so the output should only be 1 row always that would look like this: </p> <pre><code>Date: 2012-05-10 Total Players: 3 100 - 900 : 1 999 - 1500 : 1 9000 - MAX : 1 </code></pre> <p>I know how to total the tier levels with a cursor i am just wondering if there is a way to do this without one. When i try to use a SELECT CASE I am getting 1 row per record. I have tried grouping by UpdatedAt and return the same thing. Any help would be appreciated. </p> <p>Using solution 1 with the following query: </p> <pre><code>SELECT '0 - 10000' = COUNT(CASE WHEN CreditBalance &gt;= 0 AND CreditBalance &lt;= 10000 THEN 1 ELSE 0 END), '10001 - 20000' = COUNT(CASE WHEN CreditBalance &gt;= 10001 AND CreditBalance &lt;= 20000 THEN 1 ELSE 0 END), '20001 - 30000' = COUNT(CASE WHEN CreditBalance &gt;= 20001 AND SUM(CreditBalance) &lt;= 30000 THEN 1 ELSE 0 END), '30001 - 60000' = COUNT(CASE WHEN CreditBalance &gt;= 30001 AND SUM(CreditBalance) &lt;= 60000 THEN 1 ELSE 0 END), '60001 - 100000' = COUNT(CASE WHEN CreditBalance &gt;= 60001 AND SUM(CreditBalance) &lt;= 100000 THEN 1 ELSE 0 END), '100001 - 150000' = COUNT(CASE WHEN CreditBalance &gt;= 100001 AND SUM(CreditBalance) &lt;= 150000 THEN 1 ELSE 0 END), '150001 - 200000' = COUNT(CASE WHEN CreditBalance &gt;= 150001 AND SUM(CreditBalance) &lt;= 200000 THEN 1 ELSE 0 END), '200001 - 500000' = COUNT(CASE WHEN CreditBalance &gt;= 200001 AND SUM(CreditBalance) &lt;= 500000 THEN 1 ELSE 0 END), '500001 - 1000000' = COUNT(CASE WHEN CreditBalance &gt;= 500001 AND SUM(CreditBalance) &lt;= 1000000 THEN 1 ELSE 0 END), '1000001 - Max' = COUNT(CASE WHEN CreditBalance &gt;= 1000001 THEN 1 ELSE 0 END), CONVERT(DATE,LastUpdateDate) AS [Day], SUM(CreditBalance) AS TotalDailyCurrency, COUNT(PlayerID) AS DailyActiveUsers, AVG(CreditBalance) AS AverageCreditsPerMAU FROM dbo.PlayerBalances WHERE CONVERT(DATE,LastUpdateDate) = CONVERT(DATE,GETUTCDATE()) GROUP BY LastUpdateDate </code></pre> <p>and i am getting : Msg 130, Level 15, State 1, Line 7 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSSISPissesMeOff
    1. USSSISPissesMeOff
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COYour intervals are bad since you might miss users with a balance between the intervals. I.e. `10000.5`. Try using (row1) `<= 10000` and (row2) `> 10000`
      singulars
      1. POsumming up tier levels in SQL without cursor
      1. USJohan
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload