Solved

Complex sql query

Posted on 2011-02-14
9
791 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
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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Crystal report highlighting 5 29
Storage Spaces 3 39
SQL Query Conversion of IIF statement into CASE - Syntax issue 17 33
Crystal reports vb.net 2 16
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

895 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now