Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

stuck with one query in SQL Server

Tags:

sql

sql-server

I had a table named calci. The following was the sample data

CREATE TABLE calci
    (RN int, FREQ int, price int)
;

INSERT INTO calci
    (RN, FREQ, price)
VALUES
    (1, 1, 3),
    (2, 2, 4),
    (3, 3, 5),
    (4, 4, 6),
    (5, 5, 7),
    (6, 6, 8),
    (7, 1, 5),
    (8, 2, 6),
    (9, 3, 9),
    (10, 4, 7),
    (11, 5, 5),
    (12, 6, 1),
    (13, 1, 3)
;

I required only 3 records based on the sum of freq (1-6)

The result should be like

price
33 -----sum of first 6 records    
33 -----sum of next six records    
3  -----sum of last six record i.e last record
like image 885
Smart003 Avatar asked May 30 '16 10:05

Smart003


People also ask

How do I get out of a SQL query?

To exit the current iteration of a loop, you use the BREAK statement. In this syntax, the BREAK statement exit the WHILE loop immediately once the condition specified in the IF statement is met. All the statements between the BREAK and END keywords are skipped.

Why is SQL query taking so long?

There are a number of things that may cause a query to take longer time to execute: Inefficient query – Use non-indexed columns while lookup or joining, thus MySQL takes longer time to match the condition. Table lock – The table is locked, by global lock or explicit table lock when the query is trying to access it.


2 Answers

please check the following query which will solve the above problem

select sum(price) from calci  group by (rn- freq)
like image 59
Smart003 Avatar answered Sep 25 '22 14:09

Smart003


SELECT SUM(price)
FROM calci
GROUP BY (RN - 1) / 6
HAVING (RN - 1) / 6 IN (0, 1)
UNION
SELECT SUM(price)
FROM calci
WHERE (RN - 1) / 6 = (SELECT (COUNT(*) - 1) / 6 FROM calci)
like image 33
Tim Biegeleisen Avatar answered Sep 26 '22 14:09

Tim Biegeleisen