Solved

Complex sql query

Posted on 2011-02-14
9
794 Views
Last Modified: 2012-05-11
Hi,

I am stuck with this crystal report summary

This is what my table looks like

memno          bookid
    1                    11
    1                    12
    1                    13
   
    2                    14
    2                    15
   
    15                  16

    18                  17
    18                  18
    18                  19

From that table I get an SQL query

I want to get result like this

memno       bookcount      booklist               memno1-10   memno11-20    book1-10   book11-20
    1                       3             11, 12, 13                2                        2                     5                    4
    2                       2             14,15                       2                        2                     5                    4
    15                     1             16                            2                        2                     5                    4
    15                     3             17, 18, 19                2                        2                     5                    4

And do case statement

for count based on the member group

for member 1 - 10 = there are 2 members
for member 11 - 2 = there are 2 members

for member 1 - 10 there are 5 books
for member 11 - 2 = there are 4 books

I manage to display the result below using the attached query

------------------------------------------------
memno          bookcount      booklist                
    1                       3             11, 12, 13  
    2                       2             14,15  
    15                     1             16
    15                     3             17, 18, 19
------------------------------------------------

But I cant get the sql sum(count) statement to display what I wanted to


Thank You

SELECT
   t1.memno,
   BookCount = (SELECT COUNT(*)
				  FROM BookTable t2
			      WHERE t1.memno = t2.memno),
   
   BookList = substring((SELECT (',' + bookid )
                           FROM BookTable t2
                           WHERE t1.memno = t2.memno
                           ORDER BY 
                              memno,
                              bookid
                           FOR XML PATH('')
                          ), 3, 1000 )   
FROM BookTable t1                        
GROUP BY memno ORDER BY memno

Open in new window

0
Comment
Question by:elmbrook
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 34893396
Do you want SQL solution or a report based solution?

mlmcc
0
 

Author Comment

by:elmbrook
ID: 34893422
Hi mlmcc

Report based solution would be the best I think
Because I already get the fields I want

Except for the summary,

What I want is a summary like this at the bottom of the report

Member Group          memberCount                 TotalBookCount
1 - 10                        there are 2 members      there are 5 books
11 - 2                         there are 2 members      there are 4 books

Thank You








0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34893445
JUST CROSS JOIN AND pivot
sELECT MEMNO,BOOKCOUNT,BOOKLIST
      ,[memno1-10],[memno11-20],[book1-10],[book11-20]
  FROM (
SELECT
   t1.memno,
   BookCount = (SELECT COUNT(*)
				  FROM BookTable t2
			      WHERE t1.memno = t2.memno),
   
   BookList = substring((SELECT (',' + bookid )
                           FROM BookTable t2
                           WHERE t1.memno = t2.memno
                           ORDER BY 
                              memno,
                              bookid
                           FOR XML PATH('')
                          ), 3, 1000 )   
FROM BookTable t1                        
GROUP BY memno 
) AS a
CROSS jOIN (
select [memno1-10],[memno11-20],[book1-10],[book11-20]
  from (select case when memno between 1 and 10 then 'memno1-10' 
            when memno betweeb 11 and 20 then 'memno11-20'
          end as Memgrp,MEMNO
         FROM BookTable) AS X1
  PIVOT (COUNT(DISTINCT MEMNO) FOR MEMGRP IN ([memno1-10],[memno11-20])) AS P1
  CROSS jOIN
        (select case when memno between 1 and 10 then 'BOOK1-10' 
            when memno betweeb 11 and 20 then 'BOOK11-20'
          end as Memgrp,bOOKID
         FROM BookTable) AS X2
  PIVOT (COUNT(BOOKID) FOR MEMGRP IN ([book1-10],[book11-20])) AS P2
) AS b
ORDER BY 1

Open in new window

0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:elmbrook
ID: 34893493
Hi Lowfatspread,

I cant get the query working
My table only have 2 fields memno and bookid

While [memno1-10],[memno11-20],[book1-10],[book11-20] is aliases

And I am getting error in there with invalid column


Thank You
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34893513
POST THE ERROR MESSAGE
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34893551
try this
sELECT MEMNO,BOOKCOUNT,BOOKLIST
      ,[memno1-10],[memno11-20],[book1-10],[book11-20]
  FROM ( select memno,bookcount
               ,BookList = substring((SELECT (',' + bookid )
                                        FROM BookTable t2
                                       WHERE t1.memno = t2.memno
                                       ORDER BY memno,bookid
                                         FOR XML PATH('')
                                      ), 3, 1000 )   
          from (SELECT memno, count(*) as Bookcount
                  FROM BookTable                         
                 GROUP BY memno) as T1
       ) AS a
CROSS jOIN (
select [memno1-10],[memno11-20],[book1-10],[book11-20]
  from (select case when memno between 1 and 10 then 'memno1-10' 
            when memno between 11 and 20 then 'memno11-20'
          end as Memgrp,MEMNO
         FROM BookTable) AS X1
  PIVOT (COUNT(DISTINCT MEMNO) FOR MEMGRP IN ([memno1-10],[memno11-20])) AS P1
  CROSS jOIN
        (select case when memno between 1 and 10 then 'BOOK1-10' 
            when memno between 11 and 20 then 'BOOK11-20'
          end as Memgrp,bOOKID
         FROM BookTable) AS X2
  PIVOT (COUNT(BOOKID) FOR MEMGRP IN ([book1-10],[book11-20])) AS P2
) AS b
ORDER BY 1

Open in new window

0
 

Author Comment

by:elmbrook
ID: 34893612
Hi Lowfatspread,

I attached the screenshot with the error.
Please advise if there are mistakes.

I rename [memno1-10],[memno11-20],[book1-10],[book11-20]  to
totalmemno1 , totalmemno2, totalbook1 and totalbook2 so it is easier to read


Thank You


sql-error.bmp
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 34893660
ok give this a whirl
sELECT MEMNO,BOOKCOUNT,BOOKLIST
      ,[totalmemno1],[totalmemno2],[totalbook1],[totalbook2]
  FROM ( select memno,bookcount
               ,BookList = substring((SELECT (',' + bookid )
                                        FROM BookTable t2
                                       WHERE t1.memno = t2.memno
                                       ORDER BY memno,bookid
                                         FOR XML PATH('')
                                      ), 3, 1000 )   
          from (SELECT memno, count(*) as Bookcount
                  FROM BookTable                         
                 GROUP BY memno) as T1
       ) AS a
CROSS jOIN (
select [totalmemno1],[totalmemno2],[totalbook1],[totalbook2]
  from (select distinct case when memno between 1 and 10 then 'totalmemno1' 
            when memno between 11 and 20 then 'totalmemno2'
          end as Memgrp,MEMNO
         FROM BookTable) AS X1
  PIVOT (COUNT(MEMNO) FOR MEMGRP IN ([totalmemno1],[totalmemno2])) AS P1
  CROSS jOIN
        (select case when memno between 1 and 10 then 'totalBOOK1' 
            when memno between 11 and 20 then 'totalBOOK2'
          end as Memgrp,bOOKID
         FROM BookTable) AS X2
  PIVOT (COUNT(BOOKID) FOR MEMGRP IN ([totalbook1],[totalbook2])) AS P2
) AS b
ORDER BY 1

Open in new window

0
 
LVL 4

Expert Comment

by:Ztinel
ID: 34894441
Why do pivot when you can do this:
SELECT
   t1.memno,
   BookCount = (SELECT COUNT(*)
                          FROM BookTable t2
                        WHERE t1.memno = t2.memno),
   
   BookList = substring((SELECT (',' + bookid )
                           FROM BookTable t2
                           WHERE t1.memno = t2.memno
                           ORDER BY
                              memno,
                              bookid
                           FOR XML PATH('')
                          ), 3, 1000 ),
   [memno1-10] = (select count(distinct memno)
                    from #BookTable where memno<= 10),
   [memno11-20] = (select count(distinct mem)
            from #BookTable where memno> 10 ),
   [book1-10] = (select count(bookid)
            from #BookTable where memno<= 10 ),
   [book1-10] = (select count(bookid)
            from #BookTable where memno> 10 )  
FROM BookTable t1                        
GROUP BY memno ORDER BY memno
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

749 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