CODEWITHSUNDEEP
sql
Evan
Evan

Reputation: 1713

Sql query limiting number of groups

Hi i have a table with the following fields 'Site Code' 'Date' 'Hour' 'Quantity' and i want in a query to take all the inputed data of the last week. But there can be more than one inputs for one date so i will have to group according to Date first and then order those groups in DESC and take the last 7. The query i am using however does not work as it should because it orders the groups but in days with multiple inputs i am getting only 1 of them. the query is:

SELECT `Quantity` , `Hour` , `Date`
FROM (
      SELECT *
      FROM `13_trans_coffee`
      WHERE `Site Code` =103713
      GROUP BY `Date`
      ORDER BY `Date` DESC
     )days
LIMIT 0 , 7

sample data

site code  date            hour    quantity
103713     5/12/2011 0:00  21      10
103713     5/12/2011 0:00  20      11
103713     4/12/2011 0:00  14      10
103713     6/12/2011 0:00  20      10
103713     8/12/2011 0:00  23      10

notice i have 2 inputs for day 5/12/2011 but the results of the query are

quantity   hour    date
10         23      8/12/2011 0:00
10         20      6/12/2011 0:00
11         20      5/12/2011 0:00
10         14      4/12/2011 0:00

one input is missing.

Upvotes: 0

Views: 706

Answers (5)

Teja
Teja

Reputation: 13534

The below one should work.

SELECT * 
FROM `13_trans_coffee`
WHERE  `Site Code` =103713 AND `Date` 
IN
(
SELECT `Date`
FROM `13_trans_coffee`
WHERE `Site Code` =103713
GROUP BY `Date`
ORDER BY `Date` DESC
LIMIT 0 , 7
) Z;

Upvotes: 1

orbfish
orbfish

Reputation: 7731

Can you tag what DBMS you are using? I'm thinking analytic functions, but I don't know what syntax you need.

Upvotes: 0

MatBailie
MatBailie

Reputation: 86735

If you want the last 7 populated dates...

SELECT
  *
FROM
  `13_trans_coffee`
WHERE
  `Site Code` = 103713
  AND `Date` >= (SELECT MIN(`Date`) FROM (SELECT `Date` FROM `13_trans_coffee` WHERE `Site Code` = 103713 ORDER BY `DATE` DESC LIMIT 0,7))
ORDER BY
  `Date` DESC

Or...

SELECT
  *
FROM
  `13_trans_coffee`
INNER JOIN
  (SELECT `Site Code`, `Date` FROM `13_trans_coffee` WHERE `Site Code` = 103713 GROUP BY `Site Code`, `Date` ORDER BY `DATE` DESC LIMIT 0,7)  AS filter
  ON  filter.`Site Code` = `13_trans_coffee`.`Site Code`
  AND filter.`Date`      = `13_trans_coffee`.`Date`
ORDER BY
  `Date` DESC


If you want the last 7 days (plus today)...

SELECT
  *
FROM
  `13_trans_coffee`
WHERE
  `Site Code` = 103713
  AND `Date` >= CURDATE() - 7
ORDER BY
  `Date` DESC

Upvotes: 1

JonH
JonH

Reputation: 33153

This is exactly correct from a SQL standpoint. You even mentioned in your query you want to GROUP BY Date when you group by a field it finds all instances of that field and generalizes it / summarizes it.

In your sample data you group by with a value of 5/12/2011 (twice). You cannot expect to get two entries for that date with a group by date only.

You will need to group by date and hour if you want to get two entries for 5/12, the combination of both the time and the date will give you two entries (for 5/12). Please note that if you have the same hours/minutes between both tuples that this would result in one tuple as well. If you want multiple instances of a date, then you do not need to group by at all.

The only other thing I can think of is why are the times of your field all set to 0:00? If you had actual values down to the second/minute you might be able to get away with multiple tuples using a group by of date.

Upvotes: 0

parapura rajkumar
parapura rajkumar

Reputation: 24403

You are using GROUP BY incorrectly. Omit it totally for what you want.

Whenever you GROUP BY by Foo only unique Foos appear in the output which in your case happens to be the Date

Upvotes: 0

Related Questions