CODEWITHSUNDEEP
sql
SomethingsGottaGive
SomethingsGottaGive

Reputation: 1894

Using compound sql statements

I am using https://try.askgit.com/ to get statistical information about a repo. Right now I need to get the number of commits over the last month(6/21), how would I do that using the sum function?

I have this statement which queries the number of commits per author for the month and then I would just sum the count column but I am not sure how to do this in SQL:

SELECT author_name, count(*)
FROM commits 
WHERE '2021-06-01T00:00:00.000' <= author_when AND author_when <= '2021-06-30T00:00:00.000' 
GROUP BY author_name
ORDER BY count(*) DESC

Upvotes: 0

Views: 636

Answers (3)

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171411

Assuming you don't want the count per author, you can just do:

SELECT count(*) as Count
FROM commits 
WHERE '2021-06-01T00:00:00.000' <= author_when AND author_when < '2021-07-01T00:00:00.000'

Upvotes: 2

dougp
dougp

Reputation: 3087

You don't state which RDBMS you are using.
Sample input data would be helpful.

--  SQL Server
SELECT author_name
, count(*)
FROM commits 
WHERE MONTH(author_when) = 6
  AND YEAR(author_when) = 2021
GROUP BY author_name
ORDER BY CASE WHEN author_name is null then 2 else 1 end, 2 DESC

--  Oracle
SELECT author_name
, count(*)
FROM commits 
WHERE EXTRACT(MONTH FROM author_when) = 6
  AND EXTRACT(YEAR FROM author_when) = 2021
GROUP BY author_name
ORDER BY CASE WHEN author_name is null then 2 else 1 end, 2 DESC

But you also say...

and then I would just sum the count column

Do you mean in the application after getting the results?
If you want this as an additional row in your output:

--  SQL Server
SELECT author_name
, count(*)
FROM commits 
WHERE MONTH(author_when) = 6
  AND YEAR(author_when) = 2021
GROUP BY ROLLUP author_name
ORDER BY 2 DESC

--  Oracle
SELECT author_name
, count(*)
FROM commits 
WHERE EXTRACT(MONTH FROM author_when) = 6
  AND EXTRACT(YEAR FROM author_when) = 2021
GROUP BY ROLLUP author_name
ORDER BY 2 DESC

Upvotes: 2

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181290

a) Easy way is just doing two queries:

SELECT author_name, count(*)
  FROM commits 
 WHERE '2021-06-01T00:00:00.000'<=author_when AND author_when<='2021-06-30T00:00:00.000' 
 GROUP BY author_name ORDER BY count(*) DESC

And then:

SELECT count(*)
  FROM commits 
 WHERE '2021-06-01T00:00:00.000'<=author_when AND author_when<='2021-06-30T00:00:00.000'

b) If you *absolutely need this in one compound query, you can do:

SELECT author_name, count(*)
  FROM commits 
 WHERE '2021-06-01T00:00:00.000'<=author_when AND author_when<='2021-06-30T00:00:00.000' 
 GROUP BY author_name 
 UNION ALL
SELECT 'TOTAL', count(*)
  FROM commits 
 WHERE '2021-06-01T00:00:00.000'<=author_when AND author_when<='2021-06-30T00:00:00.000'

Having said that, please keep in mind that if you want your query to include last day of month (June 30th) you will need to use this literal: 2021-06-30T23:59:59.999

Upvotes: 2

Related Questions