asked on

Complex sql query

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

Mike McCracken

Do you want SQL solution or a report based solution?

mlmcc

elmbrook

ASKER

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

Lowfatspread

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

elmbrook

ASKER

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

Lowfatspread

POST THE ERROR MESSAGE

Lowfatspread

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

elmbrook

ASKER

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

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ztinel

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