Русский    English

Exercise #59

Calculate the remainder at each outlet for the database with accounts drawn not more than once a day.Result set: point, remainder.

Solution 2.2.1

  1. SELECT ss.point, ss.inc - dd.out
  2. FROM (SELECT i.point, SUM(inc) AS inc
  3. FROM Income_o i
  4. GROUP BY i.point
  5. ) AS ss,
  6. (SELECT o.point, SUM(out) AS out
  7. FROM Outcome_o o
  8. GROUP BY o.point
  9. ) AS dd
  10. WHERE ss.point = dd.point;

In the FROM clause in the each subqueries it is calculated the summa of cash income and outcome in each reception point. These subqueries are joined by the equal numbers of reception points, which allows line by line to calculate the cash balance in each point:ss.inc dd.out.

It would seem that everything is correct; however, the decision 2.2.1 contains a bug. Try to find it.


To solve the problem on SQL-EX.RU

Bookmark and Share
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.