0

I have weekly data in columns like from week 1 to Week 52 whole years data

WEEK1 WEEK2 WEEK3 WEEK4 WEEK5 WEEK6 WEEK7
40.00 43.50 40.00 40.00 40.00 43.00 46.00

I am looking to find the most common value for all these columns. Like in this example answer is 40.00

Tripti Muchhal
  • 94
  • 2
Bob
  • 15
  • 5
  • 2
    Which DBMS product are you using? "SQL" is just a query language used by all relational databases, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) –  Jun 22 '21 at 06:43
  • 2
    This would be quite easy with a properly normalized data model –  Jun 22 '21 at 06:44
  • 1
    Unpivot, count, select top. This is DBMS-specific code. – Serg Jun 22 '21 at 07:04
  • You are storing your data in columns where it should be in *rows*. You should have a `weekNumber` column and a single `value` column - your query is then trivial. – Stu Jun 22 '21 at 07:42

1 Answers1

0

Using sqlite (and most likely any other SQL database) you can convert your wide data to long data and then find the most common value.

SELECT value FROM
(
    SELECT 'Week1' AS week, week1 AS value FROM 'weeks'
    UNION ALL
    SELECT 'Week2' AS week, week2 AS value FROM 'weeks'
    UNION ALL
    SELECT 'Week3' AS week, week3 AS value FROM 'weeks'
    UNION ALL
    SELECT 'Week4' AS week, week4 AS value FROM 'weeks'
)
GROUP BY value
ORDER BY count(*) DESC
LIMIT 1;

Add more columns as necessary and maybe a where clause for filtering by year.

Since SQL doesn't natively support column wildcards you might consider converting your data from wide to long as others have pointed out in the comments.

Relevant posts:

Mode in sqlite: Calculating the mode with SQLite with grouping

Wide to long in sql (hive): Convert data from wide format to long format in SQL

GustavMH
  • 209
  • 2
  • 5