CODEWITHSUNDEEP
mysqlsqlselectcountsum
gawellus
gawellus

Reputation: 61

MySQL - count in same table

I have the following table:

+----+---------------------+---------------+
| id |     created_at      | deklaracja_id |
+----+---------------------+---------------+
|  1 | 2015-01-09 12:14:00 |             1 |/*deklaracja*/
|  2 | 2015-02-09 12:14:00 |             1 |/*korekta for 1*/
|  3 | 2015-03-09 12:14:00 |             3 |/*deklaracja/
|  4 | 2015-01-09 12:14:00 |             3 |/*korekta for 3*/
|  5 | 2015-10-09 12:14:00 |             3 |/*korekta for 3*/
|  6 | 2015-10-09 12:14:00 |             6 |/*deklaracja*/
+----+---------------------+---------------+

Cond: id = deklaracja_id is "deklaracja" id <> deklaracja_id is "korekta"

I need a query to show all "deklaracja" and count of their "korekty" later than 2015-01-09.

Ex.
+----+---------------------+---------------+
| id |     created_at      | korekty_count |
+----+---------------------+---------------+
|  1 | 2015-01-09 12:14:00 |             1 |
|  3 | 2015-03-09 12:14:00 |             2 |
|  6 | 2015-10-09 12:14:00 |             0 |
+----+---------------------+---------------+

I've tried something like:

SELECT *, 
SUM(CASE WHEN (id <> deklaracja_id)THEN 1 ELSE 0 END ) 
AS korekty_count 
FROM Deklaracja 
WHERE created >= '2015-09-01 00:00:00'

but it's not working and now I'm totally stuck :/

Upvotes: 0

Views: 1695

Answers (6)

Marco Aur&#233;lio Deleu
Marco Aur&#233;lio Deleu

Reputation: 4367

Add a GROUP BY clause to your query.

SELECT *, 
SUM(CASE WHEN (id <> deklaracja_id)THEN 1 ELSE 0 END ) 
AS korekty_count 
FROM Deklaracja 
WHERE created_at >= '2015-01-09 00:00:00' GROUP BY deklaracja_id

Upvotes: 1

Racil Hilan
Racil Hilan

Reputation: 25351

The answer by Giorgos Betsos is a good one. However, if you want to achieve the same results without a sub-query like you tried in your query, then try this one using joins:

SELECT t1.id, t1.created_at, COUNT(t2.id) AS korekty_count
FROM Deklaracja AS t1
LEFT JOIN Deklaracja AS t2 ON t1.id = t2.deklaracja_id
                           AND t2.id <> t2.deklaracja_id
WHERE t1.id = t1.deklaracja_id
  AND t1.created_at >= '2015-09-01 00:00:00' 
GROUP BY t1.id

Here is a fiddle

Upvotes: 0

Paul Kienitz
Paul Kienitz

Reputation: 878

Something like this:

  select id, created_at,
     ( select count(*) from deklaracja dt
       where dt.deklaracja_id <> dt.id
        and dt.deklaracja_id = d.deklaracja_id
        and dt.created_at >= '2015-09-01 00:00:00' ) as korekty_count
  from deklaracja d
  where id = deklaracja_id

Upvotes: 0

woz
woz

Reputation: 10994

I would use a subquery to get the records you're interested in before grouping:

SELECT
    id,
    created_at,
    COUNT(deklaracja_id) AS korekty_count
    FROM (
        SELECT id, deklaracja_id, created_at
        FROM Deklaracja 
        WHERE created_at >= '2015-09-01 00:00:00'
        AND id <> deklaracja_id
    ) tmp
    GROUP BY id;

See demo.

Upvotes: 0

starko
starko

Reputation: 1149

SELECT created_at, count(*)-1
from Deklaracje d
where id in (select id from Deklaracje e
             where e.deklaracja_id=d.deklaracja_id)
group by deklaracja_id

SQL Fiddle link

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use a correlated sub-query:

SELECT id, created_at,
       (SELECT COUNT(*) 
       FROM Deklaracja AS t2
       WHERE t1.id = t2.deklaracja_id AND 
             t2.id <> t2.deklaracja_id) AS AS korekty_count
FROM Deklaracja AS t1
WHERE id = deklaracja_id

Demo here

Upvotes: 1

Related Questions