CODEWITHSUNDEEP
sql-server
NoNaMe
NoNaMe

Reputation: 6242

Multiple COUNT SELECTS from the multiple tables in single query

I have different table 4 of them are site, job, notes and PO. Site is location and one site can have multiple jobs, job can have multiple notes and POs, now i like to select all job on the basis of site number with the count of notes and POs in single query how can i do this with single query. I used this query but this shows the PO count only. Using SQL-Server DB

SELECT jobid, job_title, po_id, count(po.po_id) PO_count, count(notes) note_count
FROM notes
LEFT JOIN job ON jobid = notes_job_id
LEFT JOIN po ON po_job_id = job_id
LEFT JOIN site_detail ON site_id = _job_si_id
LEFT JOIN site_list ON sitelist_id  = site_sitelist_id
WHERE site_id = 116291
GROUP BY  jobid, job_title, po_id

Please help, Thanks in advance,

Upvotes: 2

Views: 3876

Answers (3)

Thomas W
Thomas W

Reputation: 14154

Something like this:

select jobid, job_title, 
    (select count(po.po_id) from po where po.po_job_id=jobid) as PO_count,
    (select count(note.id) from notes where notes.notes_job_id=jobid) as note_count,
from job
where site_id = 116291

You should be able to use any number of 'subselect as column' patterns, to fetch the different counts. Another good thing about this is that it doesn't require restructuring your actual main query.

The column names & join structure aren't exact.. You'll have to fill in the gaps.

I found your DB & naming conventions unclear & rather poor. Why don't you use table names/aliases as qualifiers? This would mean you could name foreign-key columns the same in different tables, so joins would be very clear & obvious, rather than this ugly hack of prefixing them.

I'd design it very simply:

notes.FK_JOB      -> job.ID
po.FK_JOB         -> job.ID
site_jobs.FK_SITE -> site.ID
site_jobs.FK_JOB  -> job.ID

Isn't that easy & just so much simpler?

I also have no idea what a PO is, except that it is both prone to conflicts (with aliases, other columns, temporary names) and uninformative. Both due to it's extreme shortness.

Upvotes: 3

roman
roman

Reputation: 117561

I would aggregate counts before join, I think this is most efficient way:

select
    j.jobid, j.job_title, po.po_count,
    ...
from job as j
    left outer join (
        select count(*) as po_count, po.po_job_id
        from po as po
        group by po.po_job_id
    ) as po on po.po_job_id = j.job_id
    ...
where j.site_id = 116291

BTW it's really hard to read query when you're not using <table name>.<column name> notation, I strongly recommend to use aliases for tables and use dot notation for all columns

Upvotes: 0

Andomar
Andomar

Reputation: 238296

One way is to use count(distinct ...) on the primary key of the table:

SELECT  jobid
,       job_title
,       count(distinct po.po_id) po_count
,       count(distinct site.site_id) site_count
,       count(distinct note.note_id) note_count
,       ...

Upvotes: 0

Related Questions