StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<blockquote> <p>That makes a total of 3100, half of the sum listed above. If I remove "DISTINCT d.id" from the last query, every row is listed twice.</p> </blockquote> <p>While you may have only one category per dump, you therefore must have multiple <em>rows</em> in <code>dump_cat</code> per dump. You should consider defining a <code>UNIQUE</code> constraint to ensure only one row exists per pair of <code>did</code>, <code>cid</code>: </p> <pre><code>ALTER TABLE dump_cat ADD CONSTRAINT UNIQUE (did, cid); </code></pre> <p>I predict this statement will fail given the current data in your table. It can't create a unique constraint when these columns already contain duplicates!</p> <p>You can remove duplicates this way, for instance:</p> <pre><code>DELETE dc1 FROM dump_cat dc1 JOIN dump_cat dc2 USING (did, cid) WHERE dc1.id > dc2.id; -- only delete the second duplicate entry </code></pre> <hr> <p><strong>edit:</strong> By the way, don't mark my question accepted until you have verified that I'm correct! :-)</p> <p>You can verify that there are in fact duplicates as I suggest by using a query like the following:</p> <pre><code>SELECT did, COUNT(*) FROM dump_cat GROUP BY did HAVING COUNT(*) > 1; </code></pre> <p>Another possibility: you have more than one category with the same name? (sorry my first try at this query was wrong, here's an edited version)</p> <pre><code>SELECT c.name, GROUP_CONCAT(c.id) AS cat_id_list, COUNT(*) AS c FROM category c GROUP BY c.name HAVING COUNT(*) > 1; </code></pre> <hr> <p>FWIW, I did test the <code>DELETE</code> command I showed:</p> <pre><code>INSERT INTO dump_cat (did, cid) VALUES (1, 2), (3,4), (3,4); -- duplicates! DELETE dc1 FROM dump_cat dc1 JOIN dump_cat dc2 USING (did, cid) WHERE dc1.id > dc2.id Query OK, 1 row affected (0.00 sec) </code></pre> <hr> <p>PS: This is tangential to your question, but the <code>DISTINCT</code> query modifier always applies to the whole row, not just the first column. This is a common misunderstanding of many SQL programmers. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload