Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How can I write this aggregate query?  Thanks

Posted on 2014-07-14
10
Medium Priority
?
151 Views
Last Modified: 2014-07-15
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
0
Comment
Question by:goodk
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 12

Expert Comment

by:Nathan Riley
ID: 40194918
select parent, child, sum(credit), sum(debit)
from table
group by parent, child

Open in new window

0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 40194933
select parent,child,sum(credit) credits,sum(debit) debits from yourtable
group by parent,rollup(child)
order by parent,child
0
 

Author Comment

by:goodk
ID: 40196036
Oh, How do I replace the Null for the Word "Total".
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 12

Expert Comment

by:Nathan Riley
ID: 40196071
Which column is coming back null?
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 40196151
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
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 1000 total points
ID: 40196196
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
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 1000 total points
ID: 40196205
| 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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 40196221
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1000 total points
ID: 40196249
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
 

Author Closing Comment

by:goodk
ID: 40196619
Thanks everyone for your timely help
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Screencast - Getting to Know the Pipeline
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question