Geek Answers Handbook

SELECT A CASE to return cumulative results

I am trying to create a query with help SELECT CASEthat will return cumulative results.

Here is my query, it works, but does not return the correct result.

  SELECT total, count(*) as count FROM
                       (
                         SELECT case
                         WHEN ( X ) < 1 THEN '1 km'
                         WHEN ( X ) < 3 THEN '3 km'
                         WHEN ( X ) < 5 THEN '5 km'
                         WHEN ( X ) < 10 THEN '10 km'
                         WHEN ( X ) < 15 THEN '15 km'
                         WHEN ( X ) < 25 THEN '25 km'
                         WHEN ( X ) < 50 THEN '50 km'
                         WHEN ( X ) < 100 THEN '100 km'
                         WHEN ( X ) > 0 THEN '-1'
                         else '-2'
                         end AS `total` 
                         FROM `store` AS d WHERE d.pending!='1' 
                    ) AS someRandomAliasHere
                    GROUP BY `total`

X is the formula that I use to calculate the radius from lat and lang. totalNOT a column in my database table, just the result of evaluating X

The above query gives me this.

 1 km (4)
 3 km (19)
 5 km (103)
 25 km  (540)
 50 km (61)
 ....

4,19,103,540,62 - matches found.

The total counter for 3should be 19+4=23.
5should be 103+19+4=122etc. And WHEN ( X ) > 0 THEN '-1'should show the total. coincidences

I tried using BETWEEN 0 AND 1, BETWEEN 0 AND 3etc. but it still did not give me the correct results

Any ideas?

+4
9

, , :

SELECT 1 AS total, COUNT(*) AS cnt FROM `store` WHERE store.pending != 1 AND ( X ) < 1
UNION ALL
SELECT 3 AS total, COUNT(*) AS cnt FROM `store` WHERE store.pending != 1 AND ( X ) < 3
UNION ALL
SELECT 5 AS total, COUNT(*) AS cnt FROM `store` WHERE store.pending != 1 AND ( X ) < 5
UNION ALL
/** ... **/
SELECT 100 AS total, COUNT(*) AS cnt FROM `store` WHERE store.pending != 1 AND ( X ) < 100
+3

-1. , .

MySQL - . :

 SELECT total, cnt, (@cnt := @cnt + cnt) as count  
 FROM (SELECT (case  WHEN ( X ) < 1 THEN '1'
                     WHEN ( X ) < 3 THEN '3'
                     WHEN ( X ) < 5 THEN '5'
                     WHEN ( X ) < 10 THEN '10'
                     WHEN ( X ) < 15 THEN '15'
                     WHEN ( X ) < 25 THEN '25'
                     WHEN ( X ) < 50 THEN '50'
                     WHEN ( X ) < 100 THEN '100'
                     WHEN ( X ) > 0 THEN '-1'
                     else '-2'
               end) AS total, COUNT(*) as cnt
        FROM store s
        WHERE s.pending <> '1' 
        GROUP BY total
       ) t CROSS JOIN
       (SELECT @cnt := 0) vars
  ORDER BY total;

, . , . :

 SELECT total, cnt, (@cnt := @cnt + cnt) as count
 FROM (SELECT (case  WHEN ( X ) < 1 THEN '1'
                     WHEN ( X ) < 3 THEN '3'
                     WHEN ( X ) < 5 THEN '5'
                     WHEN ( X ) < 10 THEN '10'
                     WHEN ( X ) < 15 THEN '15'
                     WHEN ( X ) < 25 THEN '25'
                     WHEN ( X ) < 50 THEN '50'
                     WHEN ( X ) < 100 THEN '100'
                     WHEN ( X ) > 0 THEN '-1'
                     else '-2'
               end) AS total, COUNT(*) as cnt
        FROM store s
        WHERE s.pending <> '1' 
        GROUP BY `total`
        UNION ALL
        SELECT -1, 0
       ) t CROSS JOIN
       (SELECT @cnt := 0) vars
  ORDER BY (total >= 0) desc, total;

order by. , -2, , , X < 1 X > 0 X ( NULL). 100 , . , , .

+2

, , . .

select sum(if(X<1,1,0)) as C1,
       sum(if(X<3,1,0)) as C3,
       sum(if(X<5,1,0)) as C5,
       sum(if(X<10,1,0)) as C10,
       sum(if(X<15,1,0)) as C15,
       sum(if(X<25,1,0)) as C25,
       sum(if(X<50,1,0)) as C50,
       sum(if(X<100,1,0)) as C100,
       sum(if(X>=100,1,0)) as C100P
from store
where store.pending != '1'
+1

Unfortunatelly MySQL , :

SELECT
  total,
  cnt,
  @rollupCount:=@rollupCount+cnt AS rollupCount
FROM
  (
    SELECT
      total,
      count(*) AS cnt
    FROM
    (
         SELECT
            CASE
             WHEN ( X ) < 1 THEN '1'
             WHEN ( X ) < 3 THEN '3'
             WHEN ( X ) < 5 THEN '5'
             WHEN ( X ) < 10 THEN '10'
             WHEN ( X ) < 15 THEN '15'
             WHEN ( X ) < 25 THEN '25'
             WHEN ( X ) < 50 THEN '50'
             WHEN ( X ) < 100 THEN '100'
             WHEN ( X ) > 0 THEN '-1'
             ELSE '-2'
           END AS `total` 
         FROM
           `store` AS d
         WHERE
           d.pending != '1' 
    ) AS someRandomAliasHere
    GROUP BY
      `total`
  ) AS anotherRandomAliasHere
  , (SELECT @rollupCount:=0) AS RC
ORDER BY
  total ASC

, :

SELECT
  @rowNumber:=@rowNumber+1 AS rowNumber,
  sourceColumns
FROM
  sourceTable, (SELECT @rowNumber:=0) AS t
ORDER BY
  orderColumn;
+1

:

SELECT X,
       total,
       (SELECT SUM(total)
        FROM (<<<your_current_query>>>) ycq2
        WHERE ycq2.X <= ycq1.X) `cumulative_total`
FROM (<<<your_current_query>>>) ycq1

, .

SQL.

+1

:


/sql server version/
DECLARE @GROUPS TABLE (TOTAL INT)
INSERT INTO @GROUPS
VALUES (1),
       (3),
       (5),
       (10),
       (15),
       (25),
       (50),
       (100),
       (-1)

SELECT a.TOTAL, z.[COUNT] FROM @GROUPS a CROSS APPLY (SELECT COUNT(*) as [COUNT] FROM store x WHERE CASE WHEN a.TOTAL = -1 THEN -x.X ELSE x.X END < REPLACE(a.TOTAL,-1,0) AND pending != 1) z

/mysql version/

CREATE TEMPORARY TABLE GROUPS TABLE (TOTAL INT) INSERT INTO GROUPS VALUES (1), (3), (5), (10), (15), (25), (50), (100), (-1)

SELECT a.TOTAL, z.[COUNT] FROM GROUPS a CROSS APPLY (SELECT COUNT(*) as [COUNT] FROM store x WHERE CASE WHEN a.TOTAL = -1 THEN -x.X ELSE x.X END < REPLACE(a.TOTAL,-1,0) AND pending != 1) z

+1

. , ?

CREATE TABLE Totals (X INT, SUM INT);

INSERT INTO Totals VALUES
(1, 4),
(3, 19),
(5,  103),
(25, 540),
(50, 61)

SELECT first.X
, Sum(second.SUM)
FROM Totals first
JOIN Totals second
ON first.x >= second.x
GROUP BY first.X
UNION
SELECT 0, SUM(sum) * 2
FROM Totals

http://sqlfiddle.com/#!3/65665/12

+1

:

1.) "THEN"

2.) ( SQL SERVER)

SELECT  identity(int, 1, 1) as id,
        case
        WHEN ( X ) < 1 THEN 1
        WHEN ( X ) < 3 THEN 3
        WHEN ( X ) < 5 THEN 5
        WHEN ( X ) < 10 THEN 10
        WHEN ( X ) < 15 THEN 15
        WHEN ( X ) < 25 THEN 25
        WHEN ( X ) < 50 THEN 50
        WHEN ( X ) < 100 THEN 100
        WHEN ( X ) > 0 THEN -1
        else '-2' end AS total
Into storeID
FROM store AS d 
WHERE d.pending!='1' 
Order BY total

3.) ,

Select a.*, sum(b.total) as NewTotal
From storeID a
Left Join storeID b
    On b.id <= a.id
Group By a.id, a.total

4.) , "NewTotal" - ,

+1

, X . , , X >=0 and <1, X >=0 and <3, >=0 and <5 .., -1, >=0, - 2, <0.

I would use a table intervalsdefined as follows:

CREATE TABLE intervals (
  i_begin INT,
  i_end INT,
  i_value INT
);

INSERT INTO intervals VALUES
(0,    1,     1),
(0,    3,     3),
(0,    5,     5),
(0,    10,   10),
(0,    15,   15),
(0,    25,   25),
(0,    50,   50),
(0,    100, 100),
(0,    null, -1),
(null, 0,    -2);

or you can play with the values ​​in this table to suit your needs.

then you can just use the INNER JOIN and GROUP BY query:

SELECT
  i_value, COUNT(*)
FROM
  store INNER JOIN intervals
  ON ((i_begin IS NULL OR X>=i_begin) AND (i_end IS NULL OR X<i_end))
WHERE
  store.pending<>1
GROUP BY
  i_value

See an example here .

+1
source

More articles:


All Articles