CODEWITHSUNDEEP
sqlgroup-bycountgoogle-bigquery
JohnsonLee
JohnsonLee

Reputation: 75

SQL to calculate the overall total and sub total number

I have an example data as below.

enter image description here

I want to calculate sub total number and overall number.

Here's what I want:

enter image description here

How can I implement it in BigQuery as simple as possible?

Thanks a million!

Upvotes: 0

Views: 192

Answers (4)

Maahi
Maahi

Reputation: 326

We can get User Count using Group By [Order] column, and Total User Count using a subquery:

  Select Distinct [Order]
  ,COUNT([Order]) [User Count] 
  ,(Select Count([Order]) FROM [orders]) [Total User Count]
  FROM [orders] 
  GROUP BY  [Order]
  ORDER BY [Order] DESC

Upvotes: 0

dnoeth
dnoeth

Reputation: 60472

Aggregation plus Group Aggregate:

SELECT 
  product,
  COUNT(*),
  SUM(COUNT(*)) OVER ()
FROM test_table
GROUP BY product

Upvotes: 3

Harpal Singh
Harpal Singh

Reputation: 91

We can use an inline view of the same. Looks pretty simple.

create table test_table
(
orders varchar2(20),
userid int
)

insert into test_table values ('Phone', 1);
insert into test_table values ('Computer', 2);
insert into test_table values ('Phone', 3);
insert into test_table values ('Computer', 4);
insert into test_table values ('Computer', 5);





**select orders,count(1) as user_count,
(select count(*) from test_table) as total_user_count
from test_table
group by orders**

Upvotes: 1

someone
someone

Reputation: 75

well im going to answer this question but please don't expect anyone to answer you next time, because simply "we don't do that here" we help people, we don't do there home work. However this is your querys : In order to get the phone users number you have to type a query like this:

SELECT COUNT(order) FROM (table-name) WHERE order LIKE "phone"

In order to get the computer users number you have to type a query like this:

SELECT COUNT(order) FROM (table-name) WHERE order LIKE "computer"

i didn't understand the example you just set about total user count so im sorry i can't help you with that maybe if you be more describable.

Upvotes: 0

Related Questions