Select Query From SQL group by weekly reports

Question

I used the following query to select group by records.

SELECT  YEARWEEK(dtentered) week, sum(leftcount) as lct, sum(rightcount) as rct FROM `dailycount` WHERE paid='1'  and regid='SF00033200712' GROUP BY YEARWEEK(dtentered) "

But I am getting current week reports only. I want to show all weekly reports. I don't know how to change this.

CREATE TABLE IF NOT EXISTS `dailycount` (
    `countid` int(11) NOT NULL AUTO_INCREMENT,
    `regid` varchar(13) NOT NULL,
    `paid` int(11) NOT NULL,
    `dtentered` date NOT NULL,
    `leftcount` int(11) NOT NULL,
    `rightcount` int(11) NOT NULL,
    `carryleft` int(11) NOT NULL,
    `carryright` int(11) NOT NULL,
    `total_pairs` int(11) NOT NULL,
    `gross` int(11) NOT NULL,
    `service` decimal(19,2) NOT NULL DEFAULT '0.00',
    `net` decimal(19,2) NOT NULL DEFAULT '0.00',
    PRIMARY KEY (`countid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=33 ;


INSERT INTO `dailycount` (`countid`, `regid`, `paid`, `dtentered`, `leftcount`, `rightcount`, `carryleft`, `carryright`, `total_pairs`, `gross`, `service`, `net`) VALUES
    (6, 'SF00033200712', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
    (5, 'SF00034140513', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
    (4, 'SF00033200712', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
    (7, 'SF00035140513', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
    (8, 'SF00034140513', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
    (9, 'SF00033200712', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
    (10, 'SF00033200712', 1, '2013-05-14', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
    (11, 'SF00034140513', 1, '2013-05-14', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
    (12, 'SF00033200712', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
    (13, 'SF00037140513', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
    (14, 'SF00033200712', 1, '2013-05-14', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
    (15, 'SF00039140513', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
    (16, 'SF00037140513', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
    (17, 'SF00033200712', 1, '2013-05-14', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
    (18, 'SF00037140513', 1, '2013-05-14', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
    (19, 'SF00033200712', 1, '2013-05-14', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
    (20, 'SF00041140513', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
    (21, 'SF00037140513', 1, '2013-05-14', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
    (22, 'SF00033200712', 1, '2013-05-14', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
    (23, 'SF00039140513', 1, '2013-05-14', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
    (24, 'SF00037140513', 1, '2013-05-14', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
    (25, 'SF00033200712', 1, '2013-05-14', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
    (26, 'SF00041140513', 1, '2013-05-22', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
    (27, 'SF00037140513', 1, '2013-05-22', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
    (28, 'SF00033200712', 1, '2013-05-22', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
    (29, 'SF00042140513', 1, '2013-05-15', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
    (30, 'SF00041140513', 1, '2013-05-15', 0, 1, 0, 0, 0, 0, 0.00, 0.00),
    (31, 'SF00037140513', 1, '2013-05-15', 1, 0, 0, 0, 0, 0, 0.00, 0.00),
    (32, 'SF00033200712', 1, '2013-05-15', 1, 0, 0, 0, 0, 0, 0.00, 0.00);

I think you can remove [php] and [phpmyadmin] tags, the question is directly about sql. — Voitcus, May 15 '13 at 06:24
Change your sql as follows: SELECT sum(leftcount) as YEARWEEK(dtentered), lct,sum(rightcount) as rct FROM `dailycount` WHERE and paid='1' and regid='SF00033200712' group by YEARWEEK(dtentered); — Ertunç, May 15 '13 at 06:31

Justin · Accepted Answer · 2013-05-15T06:58:06.617

Query:

SQLFIDDLEExample

SELECT  YEARWEEK(dtentered) week,
        sum(leftcount) as lct,
        sum(rightcount) as rct 
FROM `dailycount` 
WHERE paid='1'  and regid='SF00033200712'
GROUP BY YEARWEEK(dtentered)

Result:

|   WEEK | LCT | RCT |
----------------------
| 201319 |   7 |   4 |
| 201320 |   1 |   0 |

Query:

SQLFIDDLEExample

SELECT  WEEK(dtentered) week,
        sum(leftcount) as lct,
        sum(rightcount) as rct,
 CONCAT(DATE_FORMAT(DATE_ADD(dtentered, INTERVAL(1-DAYOFWEEK(dtentered)) DAY),'%Y-%m-%e'), ' TO ',    
 DATE_FORMAT(DATE_ADD(dtentered, INTERVAL(7-DAYOFWEEK(dtentered)) DAY),'%Y-%m-%e')) AS DateRange
FROM `dailycount` 
WHERE paid='1'  and regid='SF00033200712'
GROUP BY YEARWEEK(dtentered)

Result:

| WEEK | LCT | RCT |                DATERANGE |
-----------------------------------------------
|   19 |   7 |   4 | 2013-05-12 TO 2013-05-18 |
|   20 |   1 |   0 | 2013-05-19 TO 2013-05-25 |

Thanks, it works well. I want to get weekly count only. for example 19, 20 in first column. and i need more one column to show date range. 12-05-2013 TO 18-05-2013, 19-05-2013 TO 25-05-2013 . Please help me. Advance Thanks. — Velmurugan, May 15 '13 at 06:39
Hi, I am doing the same thing and your solutions is working. But I need little more here. I also wants to those week date range that doesn't have any records. That means, count for those weeks will be come as 0. Can you please share the modified solution for this. — Dhirender, Dec 19 '17 at 05:20

Select Query From SQL group by weekly reports

1 Answers1