CODEWITHSUNDEEP
sql-server
Stephen Torres
Stephen Torres

Reputation: 13

SQL create a report providing details and summary row for each group with distinct count

I have two tables:

Table1:

company_id   customer_id   customer_name
  1                        100                  Johnson
  1                        200                  Jones
  3                        300                  Smith

Table2:

company_id   company_name
  1                      Alpha
  1                      Delta

I want to obtain a summary report with a result that will produce the following:

Result Table:

company_name   customer_id   customer_name
  Alpha                       100                  Johnson
                                  200                  Jones
  Total:  2
  Delta                       300                  Smith
  Total:  1

I have seen some sample using a sum aggregate but that errors out because I am not totaling a column. Any help is appreciated.

Upvotes: 1

Views: 663

Answers (1)

eftpotrm
eftpotrm

Reputation: 2271

SQL Server can't output the result you've asked for in a single query; with multiple results sets, formatting and repeated values missing it's really not the sort of thing it can do. I can write TSQL that'll get close to it, but you'll need to use cursors and the results will still be something that'll need specific application support to display to your end users.

You're best off for this sort of thing (IMHO) looking at tools such as SSRS or Crystal Reports, where it should be fairly straightforwards.

Upvotes: 2

Related Questions