CODEWITHSUNDEEP
t-sqlloops
Memor-X
Memor-X

Reputation: 2970

How to go through the data of a table using a where clause

I have the following database and query:

CREATE TABLE table1
    (`group` int, `points` int)
;

INSERT INTO table1
    (`group`, `points`)
VALUES
    (1, 115),
    (2, 125),
    (1, 105),
    (2, 000),
    (3, 005),
    (1, 020),
    (2, 005),
    (1, 010),
    (2, 005),
    (3, 030),
    (2, 000),
    (2, 055),
    (2, 100),
    (1, 020),
    (3, 055),
    (3, 055),
    (1, 005),
    (1, 010),
    (2, 025),
    (1, 035),
    (2, 100),
    (1, 120),
    (3, 140),
    (3, 105),
    (1, 065),
    (3, 025),
    (4, 015),
    (1, 005),
    (2, 010),
    (1, 130),
    (4, 040),
    (1, 055),
    (4, 020),
    (4, 060),
    (3, 010),
    (3, 105),
    (4, 125),
    (3, 000),
    (2, 005),
    (2, 010),
    (1, 115)
;

CREATE TABLE soruce1
    (`group` int)
;

INSERT INTO soruce1
    (`group`)
VALUES
    (1),
    (2),
    (3),
    (4)
;

  select s1.`group`, SUM(t1.`points`) FROM table1 as t1
    inner join soruce1 as s1
      on s1.`group` = t1.`group`
  where s1.`group` = 1
  GROUP BY s1.`group`
  ORDER BY SUM(t1.`points`) ASC;

Database and Query (SQL Fiddle Link)

How can I loop through all of the values in source1 with the where clause without having to use a While Loop so when it finishes running the query for group 1 it'll then move onto group 2 and so on until the end of the table, naturally, the select will be used in an Insert

This is just a sample of the data, source1 has almost 5000 entries

Upvotes: 0

Views: 64

Answers (2)

supergrady
supergrady

Reputation: 1322

You can remove that where clause and the query will aggregate the sum for each group.

SQL Fiddle

 select s1.[group], SUM(t1.points) FROM table1 as t1
    inner join soruce1 as s1
      on s1.[group] = t1.[group]
  --where s1.[group] = 1
  GROUP BY s1.[group]
  ORDER BY SUM(t1.points) ASC;

Upvotes: 1

AjV Jsy
AjV Jsy

Reputation: 6075

Comment out the WHERE line -- where s1.group = 1 and you'll get 4 rows? Is that what you mean?

Upvotes: 2

Related Questions