How can I write this aggregate query? Thanks

Parent    Child   Credit  Debit
A             A1        10              10
A             A2        10              10
A             A3        10              10
A             A1        10              10
A             A2        10              10  
A             A3        10              10
B             B1        10              10
B             B2        10              10
B             B3        10              10

How can I display results as,
Parent   Child   Credits     Debits
A                            60             60
               A1           20             20
               A2           20             20


B
.
.
.

  Can you tell me how can I write the Query to achieve this?  Or something like this.

I am using MSSQL
goodkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nathan RileyFounderCommented:
select parent, child, sum(credit), sum(debit)
from table
group by parent, child

Open in new window

0
sdstuberCommented:
select parent,child,sum(credit) credits,sum(debit) debits from yourtable
group by parent,rollup(child)
order by parent,child
0
goodkAuthor Commented:
Oh, How do I replace the Null for the Word "Total".
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Nathan RileyFounderCommented:
Which column is coming back null?
0
sdstuberCommented:
use ISNULL() function

select parent, isnull(child,'Total') child,sum(credit) credits,sum(debit) debits from yourtable
group by parent,rollup(child)
order by parent,child
0
PortletPaulfreelancerCommented:
There is no* SQL query that does exactly this:

Parent   Child   Credits     Debits
A                            60             60
               A1           20             20
               A2           20             20
               A3           20             20
B                            30             30
               B1           10             10
               B2           10             10
               B3           10             10

For that you want a report writer, or a "presentation layer"

SQL will repeat data in each column for example, and totals (if calculated this way) will be after the detailed rows, not at the top.


---
* ok ok, yes, one might write some complex set of queries to arrive at this output - perhaps. The simple message is intended to be: Your expectation from SQL is wrong/it isn't the right tool.
0
PortletPaulfreelancerCommented:
| PARENT | CHILD | CREDIT | DEBIT |
|--------|-------|--------|-------|
|      A |       |     60 |    60 |
|        |    A1 |     20 |    20 |
|        |    A2 |     20 |    20 |
|        |    A3 |     20 |    20 |
|      B |       |     30 |    30 |
|        |    B1 |     10 |    10 |
|        |    B2 |     10 |    10 |
|        |    B3 |     10 |    10 |

Open in new window

Here's what the unnatural process of matching that output looks like:
SELECT
      CASE WHEN lvl = 1 THEN parent ELSE '' END parent
    , CASE WHEN lvl = 2 THEN child ELSE '' END  child
    , credit
    , debit
FROM (
            SELECT
                  1           AS lvl
                , parent
                , NULL        AS child
                , SUM(credit) AS credit
                , SUM(debit)  AS debit
            FROM YourTable
            GROUP BY
                  parent
            UNION ALL
                  SELECT
                        2
                      , parent
                      , child
                      , SUM(credit)
                      , SUM(debit)
                  FROM YourTable
                  GROUP BY
                        parent
                      , child
      ) sq
ORDER BY
        sq.parent
      , sq.lvl
      , sq.child
;

Open in new window

http://sqlfiddle.com/#!3/cfd1b8/2
0
sdstuberCommented:
This isn't too bad, and doesn't require querying the table twice


SELECT CASE WHEN rn = 1 THEN baseparent END parent,
       ISNULL(child,'Total') child,
       credits,
       debits
  FROM (SELECT parent baseparent,
               child,
               credits,
               debits,
               ROW_NUMBER() OVER(PARTITION BY parent ORDER BY child) rn
          FROM (SELECT parent,
                       child,
                       SUM(credit) credits,
                       SUM(debit) debits
                  FROM yourtable
                GROUP BY parent, ROLLUP(child)) x) y
ORDER BY baseparent, rn

Open in new window


| PARENT | CHILD | CREDITS | DEBITS |
|--------|-------|---------|--------|
|      A | Total |      60 |     60 |
| (null) |    A1 |      20 |     20 |
| (null) |    A2 |      20 |     20 |
| (null) |    A3 |      20 |     20 |
|      B | Total |      30 |     30 |
| (null) |    B1 |      10 |     10 |
| (null) |    B2 |      10 |     10 |
| (null) |    B3 |      10 |     10 |

Open in new window

0
PortletPaulfreelancerCommented:
Very nice! Might want the null string on output

CASE WHEN rn = 1 THEN baseparent ELSE '' END parent

---
& Glad I put the footnote in earlier
(& if using sqlfiddle I encourage recording the URL)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
goodkAuthor Commented:
Thanks everyone for your timely help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.