A small SQL task with aggregate functions

Question

I'm trying to get count of C_ST's with '0' for each Abonent, but don't know how. Tried to find solutions, but nothing works..

The Structure of Database:

QRY_TYPE (ID – unique identifier of type, С_NAME – name of type, C_AB_REF – link to the abonent, who processes these types of messages).

ST_ABONENTS (ID – unique identifier, С_NAME – name)

QRY_QUEUE (ID – unique identifier, С_IN_TIME – date and time of writing msg into table, C_EXEC_TIME – date and time of the msg processing, C_ST – processing status (null - didn't, 1 – successfull, 0 – error with processing), C_QRY_TYPE – link for the query type).

Thats one of my tries, its still doesnt work

SELECT ST_ABONENTS.C_NAME AS "ABONENTNAME",COUNT(QRY_QUEUE.C_ST) AS "CNT" 
FROM ST_ABONENTS, QRY_QUEUE 
WHERE QRY_QUEUE.C_ST=0 
GROUP BY ST_ABONENTS.C_NAME 
HAVING COUNT(QRY_QUEUE.C_ST)>0;

Result should looks like this

Oracle or MySQL? Don't tag products not involved. (And one of those two has a bit odd group by behavior...) — jarlh
– jarlh, Commented Mar 10, 2016 at 8:07

Barmar · Accepted Answer · 2016-03-10 00:20:29Z

1

You're missing the joining conditions that relate the tables.

SELECT a.c_name AS ABONENTNAME, COUNT(*) AS CNT
FROM ST_ABONENTS AS a
JOIN QRY_TYPE AS t ON a.ID = t.C_AB_REF
JOIN QRY_QUEUE AS q ON q.C_QRY_TYPE = t.ID
WHERE q.C_ST = 0
GROUP BY ABONENTNAME

answered Mar 10, 2016 at 0:20

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

Comments

Kamil Gosciminski · Accepted Answer · 2016-03-10 00:21:06Z

0

This would return each abonents count with c_st = 0

select a.c_name as abonentname, count(*) as cnt
from st_abonents a
inner join qry_type qt on a.id = qt.c_ab_ref
inner join qry_queue qq on qt.id = qq.c_qry_type
where qq.c_st = 0
group by a.c_name

answered Mar 10, 2016 at 0:21

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

Collectives™ on Stack Overflow

A small SQL task with aggregate functions

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related