SQL COUNT with condition and without - using JOIN

Question

My goal is something like following table:

Key      | Count since date X | Count total
1        | 4                  | 28

With two simple selects I could gain this values: (the key of the table consists of 3 columns [t$ncmp, t$trav, t$seqn])

1. SELECT COUNT(*) FROM  db.table WHERE t$date >= sysdate-2 GROUP BY t$ncmp, t$trav, t$seqn
2. SELECT COUNT(*) FROM  db.table GROUP BY t$ncmp, t$trav, t$seqn

How can I join these statements?

What I tried:

SELECT n.t$trav, COUNT(n.t$trav), m.total FROM db.table n 
LEFT JOIN (SELECT t$ncmp, t$trav, t$seqn, COUNT(*) as total FROM db.table
           GROUP BY t$ncmp, t$trav, t$seqn) m 
ON (n.t$ncmp = m.t$ncmp AND n.t$trav = m.t$trav AND n.t$seqn = m.t$seqn)
WHERE n.t$date >= sysdate-2
GROUP BY n.t$ncmp, n.t$trav, n.t$seqn

I tried different variantes, but always got errors like 'group by is missing' or 'unknown qualifier'. Now this at least executes, but total is always 2.

T$TRAV  COUNT(N.T$TRAV) TOTAL
4       2                   2
29      3                   2
51      1                   2
62      2                   2
16      1                   2
....

If it matter, I will run this as an OPENQUERY from MSSQLSERVER to Oracle-DB.

I'd try GROUP BY n.t$trav, m.total. You typically GROUP BY the same columns as you SELECT, except those who are arguments to set functions. — jarlh
– jarlh, Commented Apr 7, 2020 at 9:44
@jarlh omg yes, that's it! thank you! Sometimes it's the obvious things :D you may post this as an answer, I'd accept it :D — Kinaeh
– Kinaeh, Commented Apr 7, 2020 at 9:49

jarlh · Accepted Answer · 2020-04-07 10:34:06Z

1

I'd try

GROUP BY n.t$trav, m.total

You typically GROUP BY the same columns as you SELECT - except those who are arguments to set functions.

answered Apr 7, 2020 at 10:34

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-04-07 11:02:19Z

1

My goal is something like following table:

If so, you seem to want conditional aggregation:

select key, count(*) as total,
       sum(case when datecol >= date 'xxxx-xx-xx' then 1 else 0 end) as total_since_x
from t
group by key;

I'm not sure how this relates to your sample queries. I simply don't see the relationship between that code and your question.

answered Apr 7, 2020 at 11:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Kinaeh Over a year ago

that's cool, I used it to get rid of the JOIN clause. :D (but used JOIN anyway to build a statement more complex...) :D

Collectives™ on Stack Overflow

SQL COUNT with condition and without - using JOIN

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related