'How Should I COUNT Tot.Num of Tours?

Table information (Primary key are bolded):

  • TOUR-GUIDE (GuideCode, Name, Surname, Nationality)
  • TYPE-OF-TOUR (TourTypeCode, Monument, Duration, City)
  • GROUP (GroupCode, NumberOfParticipants, Language)
  • GUIDED-TOUR-CARRIED-OUT (GroupCode, Date, StartTime,
    TourTypeCode, GuideCode    )

Task: For each tour guide who has never guided a type of tour for French-speaking groups, show name, and surname and, for each date, the total number of types of tours guided and their total duration.

What should I do in order to COUNT the total number of TypeofTours and their total duration?

SELECT Name, Surname 
FROM TOUR-GUIDE TG, GUIDED-TOUR-CARRIED-OUT GTCO, TYPE-OF-TOUR TOT2
WHERE GTCO.GuideCode = TG.GuideCode, AND TOT2.TourTypeCode = GTCO.TourTypeCode
  AND GuideCode NOT IN
                (SELECT GuideCode
                 FROM GROUP P, GUIDED-TOUR-CARRIED-OUT GTCO2
                 WHERE GTCO2.GroupCode = G.GroupCode
                   AND Language = 'French')
GROUP BY TG.Name, TG.Surname, GTCO.Date
HAVING COUNT(*) =
            (SELECT COUNT(*)
             FROM TYPE-OF-TOUR TOT
             GROUP BY TourTypeCode, Duration)
sqlgroup-bycounthavingpythonexcelopenpyxlworksheet


Solution 1:[1]

As I don't know the value of your table I wasn't able to run my query. If my solution doesn't work let me know.

Solution:

with cte as(
Select Duration, Date, GuideCode
From GROUP a 
left join GUIDED-TOUR-CARRIED-OUT b on a.GROUP = b.GROUP
Left join TYPE-OF-TOUR c on b.TourTypeCode = c.TourTypeCode
Where Language != 'French'   
)

Select Date, 
       Name,
       Surname, 
       sum(Duration),
       count(GuideCode) 
From TOUR-GUIDE a left join cte on a.GuideCode = cte.GuideCode
Group by 
Date, GuideCode, Name, Surname

Solution 2:[2]

When you use c.value, you specify that you only want to copy the value and not any other cell attributes (the formatting, etc). You could use copy to move all _style formatting over, eg:

from copy import copy

sheetreport.cell(row = i, column = j).value = c.value
if cell.has_style:
    sheetreport.cell(row = i, column = j)._style = copy(c._style)

...et cetera.

However, if you want to just copy the entire worksheet, I would probably just copy the entire workbook and then delete all the other worksheets, rather than iterate over every cell.

shutil.copyfile('base_template.xlsx', 'file1_to_correct.xlsx')
testreportworkbook = pyxl.load_workbook(filename='file1_to_correct.xlsx')

for ws in testreportworkbook.worksheets[1:]:
    testreportworkbook.remove_sheet(ws)

Also note, from the docs: "You also cannot copy worksheets between workbooks. You cannot copy a worksheet if the workbook is open in read-only or write-only mode."

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Shu Rahman
Solution 2 SuperScienceGrl

Related Questions