sql reporting

Question

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

veena-188827 SSC Enthusiast Points: 112 More actions · Answer 1

Hi,

What exactly you want to show on report?do u wnat to repeat dept_no with count? or just count for each dept_no?

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 2

SELECT

FROM

ORDER BY company_no dept_no

Have fun

Steve

We need men who can dream of things that never were.

sahana SSCrazy Points: 2530 More actions · Answer 3

For each company we have 4 departments.

Suppose for company 1 we have 2 departments and 2 and 3 and they have dept_no_count is 23 and 34 respectively.

But in my report If I group the data the repprt would be like this

company_id dept_no dept_no count

1 2 23

1 3 34

Because i dont have dara for dept 1 and 4.

But i shoul have a report like

company_id dept_no dept_no count

1 1 0

1 2 23

1 3 34

1 4 0

Thanks.

dongadoy SSC Eights! Points: 880 More actions · Answer 4

Do you have a table listing departments for each company?

Assuming there are always 4 depts for each company as in your example:

SELECT counts.company_no, counts.dept_no, counts.count

FROM (

SELECT dept_no = 1

UNION SELECT dept_no = 2

UNION SELECT dept_no = 3

UNION SELECT dept_no = 4

) depts

left join counts on (counts.dept_no = depts.dept_no)

ORDER BY company_no, dept_no

noeld SSC Guru Points: 96590 More actions · Answer 5

Just one minor change to snow.surfer on the select list use:

SELECT IsNull(counts.company_no,'?') as Company_no, depts.dept_no, IsNull(counts.count,0) as Counts FROM ...

HTH

* Noel