Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

Number of entries in one table if the records match the other table

T
tahishae last edited by
A table
```
 books
    id name    date_created
    1   book1  1469527903
    2   book2  1469527903
    3   book3  1469527903
```
A table
```
 sold_books 
    book_id book_price
    1       10
    1       20
    2       30
    2       21
```
That's challenge: Remove the ring from the books, unless book_id on sold_books matches book.id, and book.date_created = '1469527903'; Well, it's gonna work: 2 Because books from the idy: 1, 2 sold.
Request
```
SELECT 
count(books.id) count_sold_books
FROM 
    `books` as books
INNER JOIN
    `sold_books`
    ON
        books.id = sold_books.book_id;
```
And I get kicked out. 4 I don't know what you're talking about.
I tried LEFT JOIN and RIGHT JOIN. ♪ ♪
Reply Quote 0
1 Reply Last reply

fbio

Just like this:

SELECT 
count(DISTINCT books.id) count_sold_books
FROM 
    `books` as books
INNER JOIN
    `sold_books`
    ON
        books.id = sold_books.book_id;

But it's not gonna work everywhere.

SELECT COUNT(b.id) AS count_sold_books
FROM(
    SELECT DISTINCT books.id
    FROM `books` as books
    INNER JOIN `sold_books` ON books.id = sold_books.book_id;
    ) AS b

http://www.w3schools.com/sql/sql_func_count.asp

sql 22768

2
Posts

0
Views

Suggested Topics

J

In the entry field, the entry of symbols, other than all numbers and points, should be limited
Software Programming • python python 3.x tkinter • • jules

2

0
Votes

2
Posts

0
Views

I could finally find a answer to my question! Here are the boys!from tkinter import * def validate(new_value): try: if new_value == "" or new_value == "-" or new_value == "+": return True _str = str(float(new_value)) return True except: return False def click (): result.set('%2.3f'%(float(first.get())+float(second.get()))) tk = Tk() tk.title('проверка вводимых знаков') tk.resizable(0,0) vcmd = (tk.register(validate), '%P') canvas = Canvas(tk, width=400, height=250, bg='#e0e0e0', highlightthickness=3) canvas.pack() txt1 = Label(tk, text="Введите цифры", bg='#e0e0e0') txt2 = Label(tk, text="Введите цифры", bg='#e0e0e0') txt3 = Label(tk, text="Ответ", bg='#e0e0e0') first = StringVar() second = StringVar() result = StringVar() first_entry = Entry(textvariable=first, validate='key', validatecommand=vcmd) second_entry = Entry(textvariable=second, validate='key', validatecommand=vcmd) result_entry = Entry(textvariable=result, validate='key', validatecommand=vcmd) txt1.place(x=5, y=10) txt2.place(x=5, y=40) txt3.place(x=5, y=70) first_entry.place(x =100 , y =10) second_entry.place(x =100 , y =40) result_entry.place(x =100 , y =70) btn = Button(tk, height = 5, width = 20, text = 'посчитать', command = click) btn.place(x=5, y = 100) tk.mainloop()
K

How do you choose the number of records from another table by date in one request?
Software Programming • postgresql postgresql9. 6 • • Kadir

2

0
Votes

2
Posts

0
Views

S

SELECT date(t1.created_at) as date, t1.price, SUM(CASE WHEN type = 1 THEN 1 END) count1, SUM(CASE WHEN type = 2 THEN 1 END) count2 FROM t1 LEFT /* или INNER */ JOIN t2 ON date(t1.created_at) = date(t2.created_at) group by date(t1.created_at), t1.price UPDATE: The previous request is incorrect. SELECT t11.date, t11.price, COALESCE(SUM(CASE WHEN t2.type = 1 THEN 1 END)) count1, COALESCE(SUM(CASE WHEN t2.type = 2 THEN 1 END)) count2 FROM ( SELECT DISTINCT date(t1.created_at) as date, t1.price as price FROM t1 ) t11 LEFT /* или INNER */ JOIN t2 ON t11.date = date(t2.created_at) group by t11.date, t11.price
SQL requests for PHP
Software Programming • php mysql sql • • Demir

2

0
Votes

2
Posts

0
Views

S

$row = mysql_fetch_array($sql); echo $row[3];
O

Sampling from several tables
Software Programming • php mysql sql • • obi

2

0
Votes

2
Posts

0
Views

C

I'd move the changing environment in WHERE. This (in some scenarios) will allow for reuse of the result.Use of table pseudonymsSELECT g.id, g.name, g.info FROM games g JOIN games_platforms_assign gp On g.id = gp.game_id JOIN platforms p ON gp.platform_id = p.id JOIN games_categories_assign gc ON g.id = gc.game_id JOIN categories c ON gc.category_id = c.id WHERE p.name = ? AND c.name = ? It is absolutely necessary to create indices (a better) foreign keys(a) In the fields (a)game_id♪ platform_id♪ category_id) and indices on the search field(s)name)You can watch it. EXPLAINto see which indices are used, composite indices may be added if necessary.
J

INNER JOIN tables on unique field values of one of the tables
Software Programming • sql oracle • • Jolied

2

0
Votes

2
Posts

0
Views

R

Decision based on request:select CLIENT_DK, column2, column3, column4 from TABLE1.NAME t1 inner join (select distinct CLIENT_ID from TABLE2) t2 on t2.CLIENT_ID = t1.CLIENT_DK The request will produce a log table so that only unique ID clients remain.The decision below to the commentaries also works.
F

Updating of the record instead of creating a new one if the field is recorded
Software Programming • php mysql • • fbio

2

0
Votes

2
Posts

0
Views

J

We can use it. http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html :INSERT INTO table (id, name, date) VALUES (100, 'bla-bla', '08.01.2016 10:47') ON DUPLICATE KEY UPDATE date = '08.01.2016 10:47'; Added UNIQUE Index (or PRIMARY KEY) for the field name♪
B

SQL. Number of entries in the related table
Software Programming • sql • • Burma

2

0
Votes

2
Posts

0
Views

E

select c.*, count(e.id) from category c left join entity e on c.id = e.category_id group by c.id;
Z

WCF does not connect to SQLServer when published in IIS
Software Programming • c sharp sql iis wc languagef • • zymir

2

0
Votes

2
Posts

0
Views

B

If you have placed the correct connection string you may be having the connection blocked by the server firewall and you will have to open the door in the firewallto access the sqlserver. Follow the tutorial below. https://docs.microsoft.com/pt-br/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access
Virtual column calculation using LAG
Software Programming • sql server sql • • Laycee

2

0
Votes

2
Posts

0
Views

K

Judging by the text of the attempts, DBMS == sync, corrected by elderman == @elder_manWITH cte AS ( SELECT start, finish, start - COALESCE(LAG(finish) OVER (ORDER BY start ASC), MIN(start) OVER ()) delta FROM test ) SELECT start, finish, SUM(CASE WHEN delta > 5 THEN 1 ELSE 0 END) OVER (ORDER BY start ASC) group_num FROM cte https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=c86a6c6fe11771ad7e215d996558178f PS. The model uses INT-- that's what I want to do, and the author didn't care. Accordingly, the CTE should be used instead of deducting DATEDIFF(MINUTE, ...)
Z

Exchange WHILE to GROUP in SQL
Software Programming • sql server sql • • zymir

2

0
Votes

2
Posts

0
Views

R

I need to count and group for the first two digits. I need this separated for months...Rate-- código #1 v2 USE CCILCDatabase; go SELECT month(DateCreated) as Mes, convert(char(2), CPCCodeID) as Alg2, count(*) as Estat from PublicContestCPC where DateCreated between '20170101' and '20171231' group by month(DateCreated), convert(char(2), CPCCodeID) order by Mes, Alg2; The above code considers that the column DateCreated It's the kind date and that the column CPCCodeId has at least 2 digits.
C

Slowly find the number of records of a table
Software Programming • php css • • charlenef

2

0
Votes

2
Posts

0
Views

A

Use the function https://developer.mozilla.org/en-US/docs/Web/API/WindowTimers/setInterval from JavaScript:var contador = document.getElementById('contador'); var TOTAL_CLIENTES = 10; var intervalo = setInterval(function() { contador.innerHTML = parseInt(contador.innerHTML, 10) + 1; if (contador.innerHTML == TOTAL_CLIENTES) { clearInterval(intervalo); } }, 500);<p>Clientes cadastrados: <span id="contador">0</span></p>The above code will increase the value of <span> in 1 to 500 milliseconds. You can change the increment range as you want, leave faster or slower.TOTAL_CLIENTES is your value coming from PHP:var TOTAL_CLIENTES = <?php echo $admcadastros; ?>;
E

Dump Database
Software Programming • sql server sql dump • • emerson

2

0
Votes

2
Posts

0
Views

M

Make a SELECT in table backupset:SELECT b.database_name AS base, CASE b.type WHEN 'D' THEN 'Completo' WHEN 'L' THEN 'Log' WHEN 'I' THEN 'Diferencial' ELSE 'Outro' END tipo, CONVERT(VARCHAR, b.backup_finish_date, 103) + ' ' + CONVERT(VARCHAR, b.backup_finish_date, 108) AS fim FROM msdb.dbo.backupset b ORDER BY b.database_name, b.backup_finish_date DESC https://technet.microsoft.com/pt-br/library/ms186299(v=sql.110).aspx Contains a line for each backup set. A backup set contains the backup of a single successful backup operation.
Build a linear list from the entry sequence of the numbers. Remove all the numbers except the ones that are simple.
Software Programming • c++ algorithm single contact list • • morde

2

0
Votes

2
Posts

0
Views

A

This cycle of removal of nodes from the list that do not contain simple numbers r=fr; rp=r; while (r->next != NULL) // Пока не дошли до последнего элемента списка. { for (int i=1;i<r->inf;i++){ if (r->inf%i==0 && i!=1 && i!=r->inf) { rp=r; r=r->next; cout<<"\n3\n"; cout<<rp; break; } else{ if (r==fr) { fr=r->next; delete rp; rp=fr; r=fr; cout<<"\n1\n"; break; } else { rp->next=r->next; delete r; r=rp->next; cout<<"\n2\n"; break; } } cout<<r; } } Doesn't make sense. First fr Maybe even. NULLand then computing the conditions r->next != NULL lead to an uncertain programme behaviour. Second, Fr->next in turn may be equal NULLAnd then you won't be able to check whether the only unit on the list contains a simple number or not.Then you're wrong to check if the number is simple. For example, number 2 is simple and number 1 is not simple.So you'd better write a separate function that checks whether the number is simple or not. As a result, you'll have a simple code and it'll be easier to read.Conditions if_else to be checked after the cycle has been completed forand not at all his irration.EDIT: A function that checks whether a positive number is simple can be as shown in the demonstration programme below.#include <iostream> bool is_prime( int value ) { bool prime = ( value == 2 ) || ( value > 2 && value % 2 ); for ( int i = 3; prime && i <= value / i; i += 2 ) { prime = value % i; } return prime; } int main() { const int N = 100; for ( int i = 1; i < N; i++ ) { if ( is_prime( i ) ) std::cout << i << ' '; } std::cout << std::endl; return 0; } Her withdrawal to the console2 3 5 7 11 13 17 19 23 29 31 37 41 43 47 53 59 61 67 71 73 79 83 89 97 When using this function, the cycle while can look likewhile ( r != NULL ) { if ( not is_prime( r->inf ) ) { // удаляем узел // ... } else { // просто переходим к следующему узлу rp = r; r = r->next; } } Identifiers should be given names so that the reader of your code would understand what they mean. Identifiers like fr or erthe ones you use are difficult to name.The variables in the smallest area of visibility should also be declared. Otherwise, the announcement of many variables can only complicate reading your code.If you use your approach, the program can look like it's the only way I'm putting data on the list, not from the file, but just using a natural series of numbers.#include <iostream> bool is_prime( int value ) { bool prime = ( value == 2 ) || ( value > 2 && value % 2 ); for ( int i = 3; prime && i <= value / i; i += 2 ) { prime = value % i; } return prime; } struct node { int inf; node *next; }; int main() { node *head = nullptr, *tail = nullptr; const int N = 100; int i = 1; do { node *current = new node { i, nullptr }; if ( head == nullptr ) head = current; else tail->next = current; tail = current; } while ( i++ != N ); for ( node *current = head; current != nullptr; current = current->next ) { std::cout << current->inf << ' '; } std::cout << std::endl; node *previous = head, *current = head; while ( current != nullptr ) { if ( not is_prime( current->inf ) ) { if ( current == head ) { head = current->next; delete current; current = head; previous = head; } else { previous->next = current->next; delete current; current = previous->next; } } else { previous = current; current = current->next; } } std::cout << std::endl; for ( node *current = head; current != nullptr; current = current->next ) { std::cout << current->inf << ' '; } std::cout << std::endl; while ( head != nullptr ) { node *current = head; head = head->next; delete current; } return 0; } The programme ' s withdrawal is as follows:1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 2 3 5 7 11 13 17 19 23 29 31 37 41 43 47 53 59 61 67 71 73 79 83 89 97 However, this approach to removing nodes from the list is not a good one. Why? Because the general algorithm deals with private cases: whether the removed knot is the head of the list.Programming is the art of synthesis. The more the programme deals with private cases, the more the programme is exposed to errors and the readership of the programme code is difficult to catch a common idea. He's also gonna have to spend his time digging these people to figure out what's going on. It is harder to maintain and modify such programmes.Therefore, programmes should be designed to avoid the frequency that is not really necessary. We need to choose a more general algorithm that is not overburdened by any private matter, simply in which fewer different situations occur. if-else proposals.Your program can use another approach to remove the knots from a single link list. Below is the demonstration programme#include <iostream> bool is_prime( int value ) { bool prime = ( value == 2 ) || ( value > 2 && value % 2 ); for ( int i = 3; prime && i <= value / i; i += 2 ) { prime = value % i; } return prime; } struct node { int inf; node *next; }; int main() { node *head = nullptr; node *tail = nullptr; const int N = 100; for ( int i = 1; i <= N; i++ ) { node *current = new node { i, nullptr }; if ( head == nullptr ) head = current; else tail->next = current; tail = current; } for ( node *current = head; current != nullptr; current = current->next ) { std::cout << current->inf << ' '; } std::cout << std::endl; for ( node **current = &head; *current != nullptr; ) { if ( not is_prime( ( *current )->inf ) ) { node *tmp = *current; *current = ( *current )->next; delete tmp; } else { current = &( *current )->next; } } std::cout << std::endl; for ( node *current = head; current != nullptr; current = current->next ) { std::cout << current->inf << ' '; } std::cout << std::endl; while ( head != nullptr ) { node *current = head; head = head->next; delete current; } return 0; } As can be seen from the removal of the nodes, no private case is considered as to whether the knot is the head, as the removal algorithm is the same for all nodes.It would be interesting to read the discussion on one site on the topic. http://www.rsdn.org/forum/flame.comp/6596405 Reference is made to the address by the author of the Linux Linus Torvalds Operative System, where he, inter alia, expressed his opinion on two fragments of a code related also to the list.Thus, the participants in this forum who consider themselves professional, first of all, did not realize that the example of the code was conditional and intended to demonstrate the idea. And secondly, I didn't understand that idea, that's what Linus means when one fragment of the code feels better than the other.And Linus means exactly what I wrote here: try to write a code synthesized, avoiding unnecessary frequency, which will only overload the programme and thus distort the main and secondary, making it more difficult to see the essence of the algorithm.
How to avoid reordering a table after updating one of your records?
Software Programming • postgresql nodejs express sequelize.js • • Mystic

2

0
Votes

2
Posts

0
Views

A

Most database engines do not secure a specific order, they are ordered by engine decision as they want to ensure consistency of data. On the other hand, the order is irrelevant because, in your manager, you can order any column.If you need a specific order when recovering the data, Never trust in the database engine criterion, instead, a ORDER BY to order by the column required.return Modelo.findAll({ order: '<campo> <orientación>' }); Where:field is the field of the model (not the column)ordained guidance (ASC/DESC)My other question is: Why do you use a middleware to update? Note that, a middleware is intended to always run before the function associated with a specific route is executed, so that middleware will be executed multiple times, which makes unnecessary operations. It should be a normal route:app.put('/:id', (request, response) => { let id = request.params.id; // actualizar }); If your purpose is to make lectura be a shared object through all customers, then you can encapsulate that object in a module and modify it./* shared/reading.js */ const lectura = /* valor inicial */; const LecturaHandler = () => { return { get () { return lectura; }, update ({ lectura_actual, texto }, cb) { lectura.lectura_actual = lectura_actual; lectura.texto = texto; return lectura.save(); } }; } const instance = LecturaHandler(); Object.freeze(instance); export default instance; And you'd update like this:const LecturaHandler = require('./shared/reading.js'); app.put('/:id', (req, res) => { LecturaHandler.update(req.body, (promise) => { promise .then(lectura => { res.redirect(/partes/${req.params.parteId}); }) .catch(err => res.jsonp(err.errors)); }); });
C

PHP - How to make the left join with more than one record in the other tables do not return repeated values in the columns?
Software Programming • php mysql join query • • chanisef

2

0
Votes

2
Posts

0
Views

S

This is not a SQL problem, the moment you make a join you are generating a https://pt.wikipedia.org/wiki/Produto_cartesiano .What I can suggest is during data processing, display only the information you don't want you to repeat on the first occasion of the customer ID.I don't know how your code is, but it's a generic example:<?php $rows = array(); $row['cliente_id'] = 1; $row['nome'] = 'fulano'; $row['saldo'] = 10; $row['valor'] = 1; $rows[] = $row; $row['cliente_id'] = 1; $row['nome'] = 'fulano'; $row['saldo'] = 10; $row['valor'] = 2; $rows[] = $row; $row['cliente_id'] = 1; $row['nome'] = 'fulano'; $row['saldo'] = 10; $row['valor'] = 3; $rows[] = $row; $row['cliente_id'] = 2; $row['nome'] = 'fulano'; $row['saldo'] = 10; $row['valor'] = 1; $rows[] = $row; $row['cliente_id'] = 2; $row['nome'] = 'fulano'; $row['saldo'] = 10; $row['valor'] = 2; $rows[] = $row; echo "<table>"; echo "<tr>"; echo "<th>ID</th>"; echo "<th>Nome</th>"; echo "<th>Saldo</th>"; echo "<th>Valor</th>"; echo "</tr>"; $last_id = null; foreach($rows as $row){ echo "<tr>"; echo $last_id != $row['cliente_id'] ? "<td>$row[cliente_id]</td>" : "<td></td>"; echo "<td>$row[nome]</td>"; echo $last_id != $row['cliente_id'] ? "<td>$row[saldo]</td>" : "<td></td>"; echo "<td>$row[valor]</td>"; echo "</tr>"; $last_id = $row['cliente_id']; } echo "</table>"; If you post your code, how you are riding the table we can help in the real case.

Number of entries in one table if the records match the other table

Suggested Topics

In the entry field, the entry of symbols, other than all numbers and points, should be limited Software Programming • python python 3.x tkinter • • jules

How do you choose the number of records from another table by date in one request? Software Programming • postgresql postgresql9. 6 • • Kadir

SQL requests for PHP Software Programming • php mysql sql • • Demir

Sampling from several tables Software Programming • php mysql sql • • obi

INNER JOIN tables on unique field values of one of the tables Software Programming • sql oracle • • Jolied

Updating of the record instead of creating a new one if the field is recorded Software Programming • php mysql • • fbio

SQL. Number of entries in the related table Software Programming • sql • • Burma

WCF does not connect to SQLServer when published in IIS Software Programming • c sharp sql iis wc languagef • • zymir

Virtual column calculation using LAG Software Programming • sql server sql • • Laycee

Exchange WHILE to GROUP in SQL Software Programming • sql server sql • • zymir

Slowly find the number of records of a table Software Programming • php css • • charlenef

Dump Database Software Programming • sql server sql dump • • emerson

Build a linear list from the entry sequence of the numbers. Remove all the numbers except the ones that are simple. Software Programming • c++ algorithm single contact list • • morde

How to avoid reordering a table after updating one of your records? Software Programming • postgresql nodejs express sequelize.js • • Mystic

PHP - How to make the left join with more than one record in the other tables do not return repeated values in the columns? Software Programming • php mysql join query • • chanisef

In the entry field, the entry of symbols, other than all numbers and points, should be limited
Software Programming • python python 3.x tkinter • • jules

How do you choose the number of records from another table by date in one request?
Software Programming • postgresql postgresql9. 6 • • Kadir

SQL requests for PHP
Software Programming • php mysql sql • • Demir

Sampling from several tables
Software Programming • php mysql sql • • obi

INNER JOIN tables on unique field values of one of the tables
Software Programming • sql oracle • • Jolied

Updating of the record instead of creating a new one if the field is recorded
Software Programming • php mysql • • fbio

SQL. Number of entries in the related table
Software Programming • sql • • Burma

WCF does not connect to SQLServer when published in IIS
Software Programming • c sharp sql iis wc languagef • • zymir

Virtual column calculation using LAG
Software Programming • sql server sql • • Laycee

Exchange WHILE to GROUP in SQL
Software Programming • sql server sql • • zymir

Slowly find the number of records of a table
Software Programming • php css • • charlenef

Dump Database
Software Programming • sql server sql dump • • emerson

Build a linear list from the entry sequence of the numbers. Remove all the numbers except the ones that are simple.
Software Programming • c++ algorithm single contact list • • morde

How to avoid reordering a table after updating one of your records?
Software Programming • postgresql nodejs express sequelize.js • • Mystic

PHP - How to make the left join with more than one record in the other tables do not return repeated values in the columns?
Software Programming • php mysql join query • • chanisef