?
Solved

Complex sql query

Posted on 2011-02-14
9
Medium Priority
?
796 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 101

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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 2000 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

770 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