loading..
Русский    English
23:34

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

Console
Execute
  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.

T&S

To solve the problem on SQL-EX.RU

Bookmark and Share
Tags
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 CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100