Solved

Complex sql query

Posted on 2011-02-14
9
790 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

758 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