logo
Tags down

shadow

List of items using count and average in MYSQL Workbench producing too few results


By : user2176021
Date : October 14 2020, 02:23 PM
Hope that helps I have a list of book titles that I need to first count more than four copies per title (in alphabetical order), then total the book title and find the average price. However, my code only produces 1 book, instead of a list. This is what I need to produce: , You are missing a GROUP BY Book_Title statement.
code :
SELECT 
  Book_Title AS 'Title',
  COUNT(Copy_Num) AS 'Count',
  Round(Avg(Copy_Price), 2) AS 'Average Price'
FROM BOOK
  JOIN COPY USING (Book_Code)
  JOIN BRANCH USING (Branch_Num)
GROUP BY Book_Title
HAVING Count(Copy_Num) > 4
ORDER BY Book_Title;


Share : facebook icon twitter icon

MySQL Workbench - producing bad SQL?


By : ciceronium
Date : March 29 2020, 07:55 AM
wish help you to fix your issue I'm trying to make a table that has a "created" timestamp and an "updated" timestamp (and have MySQL insert values in there automatically for me). It's doable, see: http://gusiev.com/2009/04/update-and-create-timestamps-with-mysql/.
code :
stamp_created TIMESTAMP NULL DEFAULT 0,
stamp_created TIMESTAMP NULL DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP,
  stamp_created TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,

MySQL - how to get results back from a table, and the count of how many joined items there are in one query?


By : Gabriel OR
Date : March 29 2020, 07:55 AM
hop of those help? I have a query like this: , UPDATED with outer join:
code :
select s.display_order, s.section_name, s.solution_section_id
      ,count(c.comment_id) AS comment_count
  from solution_sections s
  left outer join suggested_solution_comments c ON (c.solution_part = s.solution_section_id)
  group by s.display_order, s.section_name, s.solution_section_id
  order by display_order

SQL Server AVG and Excel AVERAGE producing different results?


By : Mladen Sijan
Date : March 29 2020, 07:55 AM
I wish this help you I'm trying to show averages on SQL server, but when I test the data in Excel the results are not the same, there must be something obvious I am missing. , Try this for fun:
code :
select avg(a)
from
    (values(1),(2),(3),(4)) x(a);

avg(a)
-------
2
select avg(cast(a as decimal(10,5)))
from
    (values(1),(2),(3),(4)) x(a);

result
--------
2.5
SELECT d.d_name, sub.GroupSize AS FacultyAverage
FROM unitesnapshot.dbo.capd_register r
INNER JOIN unitesnapshot.dbo.capd_studentregister sr ON sr.sr_register = r.r_id
INNER JOIN unitesnapshot.dbo.capd_activity a ON a.a_register = r.r_id
INNER JOIN unitesnapshot.dbo.capd_moduleactivity ma ON ma.ma_activity = a.a_id
INNER JOIN unitesnapshot.dbo.capd_module m ON m.m_id = ma.ma_activitymodule
INNER JOIN unitesnapshot.dbo.capd_department d ON d.d_id = m.m_moduledept
INNER JOIN unitesnapshot.dbo.capd_section sec ON sec.s_id = m.m_modulesection
INNER JOIN (SELECT r.r_reference,
            COUNT(DISTINCT s.s_studentreference) AS GroupSize
            FROM unitesnapshot.dbo.capd_student s
            INNER JOIN unitesnapshot.dbo.capd_person p ON p.p_id = s.s_id
            INNER JOIN unitesnapshot.dbo.capd_studentregister sr ON sr.sr_student = p.p_id
            INNER JOIN unitesnapshot.dbo.capd_register r ON r.r_id = sr.sr_register
            GROUP BY r.r_reference) sub ON sub.r_reference = r.r_reference
WHERE SUBSTRING(r.r_reference,4,2) = '12' AND d.d_reference = '730'
ORDER BY d.d_name

Getting average times in mySQL workbench query


By : user2638041
Date : March 29 2020, 07:55 AM
seems to work fine I have a table in mySQL workbench that has several columns and 11 million rows. , I think you just want a basic aggregation query:
code :
SELECT DATE_FORMAT(`timestamp`, '%Y-%m') as yyyymm,
       SUM(app_type = 'API') as API,
       SUM(app_type = 'Web') as Web,
       SUM(app_type = 'Excel') as Excel
FROM access_log
WHERE `action` = 'download' AND org_id <> 1
GROUP BY yyyymm;

How to group by single words and count average score for a list containing tuple items?


By : user3440693
Date : March 29 2020, 07:55 AM
this one helps. You can convert your list to a data frame and use groupby to calculate the mean score.
code :
df = pd.DataFrame(list)
df.groupby([0]).agg("mean")
              1
    0   
attended    0.512065
benefactor  0.484747
collaborator    0.594132
ex-wife     0.676683
marital     0.560252
parenthood  0.608871
procreation     0.571285
unmarried   0.564648
warship     0.474377
Related Posts Related Posts :
  • Create a user defined function that works with GROUP BY in mysql
  • Faster process for large insert
  • Unable to configure mysql database, after cloud sql proxy setup
  • (Amazon Linux) MySQL stops working on EC2 reboot
  • SQL procedure for inserting records in a table based on records in another table
  • How to name SQL FOREIGN KEY Constraint on CREATE two tables columns into a relationship TABLE
  • Sum, Subtract and Join of multiple mysql table columns
  • calculate standard deviation inside pivot table
  • Docker - mysqld: Table 'mysql.plugin' doesn't exist
  • Determine max number of overlapping DATETIME ranges
  • Puppet: file_line evaluates os.path.join
  • Insert data into blog in WordPress by Json link
  • filter all two tables to get all the data
  • MySQL SUBSTRING() with non-utf8 encoding
  • How to dump and download mysql from Google Clould's VM
  • Add int to date SQL
  • MySQL - Find the lowest unique value in table
  • How to apply case when with if condition in MySql
  • SQL Count valid Transactions per User
  • How to take some rows in the manual order and then rest in the regular way
  • Check if date colum is in some period which depends on another date column
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org