Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Complex sql query

Posted on 2011-02-14
9
Medium Priority
?
798 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

609 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